更新Snowflake中ARRAY列中的一个键值

尝试更新键值所在的所有行的数组列 dl_country 为空:

UPDATE tgt
SET DETAILS_ARRAY = NEW_DETAILS_ARRAY
FROM (
  SELECT 
    a_id,
    d_id,
    ARRAY_AGG(
      CASE 
        WHEN json_data.value:dl_country::string = '' THEN OBJECT_INSERT(json_data.value, 'dl_country', src.dl_country)
        ELSE json_data.value
      END
    ) AS NEW_DETAILS_ARRAY
  FROM tgt,
  LATERAL FLATTEN(input => DETAILS_ARRAY) json_data,
  (
    SELECT a_id, d_id, dl_country
    FROM doc_fix
    WHERE dl_country = ''
  ) src
  WHERE tgt.a_id = src.a_id
  AND tgt.d_id = src.d_id
  GROUP BY a_id, d_id
) AS subquery
WHERE tgt.a_id = subquery.a_id
AND tgt.d_id = subquery.d_id;