如何在Snowflake SQL中查询嵌套JSON数组内包含指定字段的对象

假设存储JSON数据的表名为your_table，JSON内容对应的字段名为json_col，可通过以下SQL实现需求：

SELECT emp.value AS employee_with_photo
FROM your_table,
LATERAL FLATTEN(input => json_col:employees.employee) emp
WHERE OBJECT_HAS_KEY(emp.value, 'photo') = TRUE;

逻辑说明 #

• LATERAL FLATTEN 函数用于将嵌套在JSON中employees下的employee数组拆分为单行单条的员工对象，拆分后的单个员工对象存储在emp.value字段中
• OBJECT_HAS_KEY 是Snowflake原生提供的JSON对象校验函数，入参为待校验的JSON对象和要检查的字段名，返回布尔值表示该对象是否包含对应字段
• 筛选条件仅会保留包含photo字段的员工对象，完全匹配你的输出要求

如果你需要将员工字段展开为独立的关系型列返回，可使用如下变体写法：

SELECT 
 emp.value:id::STRING AS employee_id,
 emp.value:firstName::STRING AS first_name,
 emp.value:lastName::STRING AS last_name,
 emp.value:photo::STRING AS photo_url
FROM your_table,
LATERAL FLATTEN(input => json_col:employees.employee) emp
WHERE OBJECT_HAS_KEY(emp.value, 'photo') = TRUE;

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