Snowflake中如何展开JSON地址数组生成多行数据？

你需要将JSON中的addresses数组展开，让每个地址对应一行数据，正确的做法是用LATERAL FLATTEN来拆解数组，同时调整JSON路径的访问方式。下面是修正后的SQL：

SELECT
    REPLACE(DOCUMENT:"_id"::VARCHAR(50),'guests-','') AS GUEST_ID,
    PARSE_JSON(DOCUMENT):"_rev"::STRING AS GUEST_REVISION_ID,
    addr.value:address_id::VARCHAR(255) AS ADDRESS_ID,
    addr.value:address_type::VARCHAR(255) AS ADDRESS_CODE,
    UPPER(REGEXP_REPLACE(addr.value:address_line1::VARCHAR(255),'[\n\r]','')) AS ADDRESS_LINE_1,
    UPPER(REGEXP_REPLACE(addr.value:address_line2::VARCHAR(255),'[\n\r]','')) AS ADDRESS_LINE_2,
    UPPER(REGEXP_REPLACE(addr.value:city::VARCHAR(255),'[\n\r]','')) AS CITY_NAME,
    UPPER(addr.value:state::VARCHAR(255)) AS STATE_CODE,
    UPPER(addr.value:country::VARCHAR(255)) AS COUNTRY,
    addr.value:postal_code::VARCHAR(255) AS POSTAL_CODE,
    UPPER(addr.value:country_code::VARCHAR(255)) AS COUNTRY_CODE,
    UPPER(addr.value:first_name::VARCHAR(255)) AS ADDRESS_FIRST_NAME,
    UPPER(addr.value:last_name::VARCHAR(255)) AS ADDRESS_LAST_NAME,
    addr.value:phone_number::VARCHAR(255) AS PHONE_NUMBER,
    -- 布尔值直接转INT更简洁，true=1，false=0
    addr.value:primary::INT AS FLAG
FROM test,
LATERAL FLATTEN(INPUT => PARSE_JSON(DOCUMENT):personal_info:addresses) addr
-- 如果需要去重重复地址，保留DISTINCT即可
-- DISTINCT ON (GUEST_ID, ADDRESS_ID)

关键修改说明： #

• 用LATERAL FLATTEN拆解数组：通过LATERAL FLATTEN(INPUT => PARSE_JSON(DOCUMENT):personal_info:addresses)把addresses数组拆成单个对象，用别名addr指代每个地址元素。
• 调整JSON路径：原SQL中"addresses[]"的写法错误，现在通过addr.value访问每个地址对象的属性，路径更清晰。
• 简化布尔值处理：把原有的CASE语句替换为addr.value:primary::INT，因为Snowflake中布尔值true转INT是1，false转INT是0，更简洁高效。
• 清理冗余语法：去掉了原SQL中重复的SELECT关键字，以及开头多余的逗号。如果你的数据存在重复地址，可以取消注释最后一行的DISTINCT ON来去重。

内容的提问来源于stack exchange，提问作者Bharat