Snowflake中JSON扁平化：Country对象数据提取求助

一、关联City数据提取Country信息 #

由于country是JSON顶层的对象（非数组），不需要用LATERAL FLATTEN，直接通过JSON路径访问即可。可以把Country字段加到你已有的City查询中，让每个City行都带上对应的国家信息：

SELECT
  -- 原有City相关字段
  f.VALUE:city_description:text:st::STRING AS city_description,
  f.VALUE:city_land:st:st::STRING AS city_land,
  f.VALUE:conception_date:st:st::STRING AS conception_date,
  -- Country相关字段
  t.PARSED_DATA:country:country_code:name_of_country:st::STRING AS country_name,
  t.PARSED_DATA:country:country_code:desc:st::STRING AS country_desc,
  t.PARSED_DATA:country:country_identifier:id:id::STRING AS country_identifier,
  t.PARSED_DATA:country:country_description:st:st::STRING AS country_description,
  t.PARSED_DATA:country:country_id:si:si::STRING AS country_id
FROM tableinsnowflake t,
LATERAL flatten(input => t.PARSED_DATA, path => 'city') f

二、提取Country内的数组数据（如country_type下的is数组） #

如果要提取country里嵌套的数组（比如country_type.is），需要针对这个数组单独使用LATERAL FLATTEN：

SELECT
  -- 基础Country信息
  t.PARSED_DATA:country:country_code:name_of_country:st::STRING AS country_name,
  t.PARSED_DATA:country:country_code:desc:st::STRING AS country_desc,
  -- 展开country_type下的is数组
  ct.VALUE:is::STRING AS country_type_code
FROM tableinsnowflake t,
LATERAL flatten(input => t.PARSED_DATA:country:country_type:is, path => '') ct

关键说明 #

• Snowflake用冒号:逐层访问JSON结构，比如PARSED_DATA:country:country_code:name_of_country:st就是从顶层到目标字段的完整路径
• 用::STRING显式将VARIANT类型转为字符串，方便后续使用
• 若Country下还有其他数组，都可以用同样的方式，针对数组路径做LATERAL FLATTEN

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