如何在Snowflake SQL中筛选JSON文档内的Manager字段值

可行方案1：FLATTEN展开嵌套数组（推荐，扩展性强） #

通过两次LATERAL FLATTEN分别展开外层company数组和内层company_data数组，再筛选目标记录，逻辑清晰且支持多场景扩展：

SELECT
    c_data.value:Org::STRING AS COMPANY,
    c_data.value:Name::STRING AS MANAGER,
    c_data.value::VARIANT AS CONTENTS
FROM
    poorly_imagined_table,
    -- 展开外层company数组
    LATERAL FLATTEN(input => company) AS c,
    -- 展开每个company下的company_data数组
    LATERAL FLATTEN(input => c.value:company_data) AS c_data
WHERE
    c_data.value:Position::STRING = 'Manager';

如果需要确保每个公司仅返回一条Manager记录（比如取第一个匹配项），可以添加QUALIFY子句：

SELECT
    c_data.value:Org::STRING AS COMPANY,
    c_data.value:Name::STRING AS MANAGER,
    c_data.value::VARIANT AS CONTENTS
FROM
    poorly_imagined_table,
    LATERAL FLATTEN(input => company) AS c,
    LATERAL FLATTEN(input => c.value:company_data) AS c_data
WHERE
    c_data.value:Position::STRING = 'Manager'
QUALIFY
    ROW_NUMBER() OVER (PARTITION BY c_data.value:Org::STRING ORDER BY c_data.index) = 1;

可行方案2：数组函数直接提取（无需展开数组） #

如果不需要展开所有数组元素，可使用Snowflake数组函数在SELECT语句内直接筛选：

SELECT
    -- 假设每个company的Org统一，取第一个元素的Org
    company[0]:company_data[0]:Org::STRING AS COMPANY,
    -- 筛选Position为Manager的记录并提取第一个的Name
    ARRAY_FIRST(ARRAY_FILTER(
        company[0]:company_data,
        x -> x:Position::STRING = 'Manager'
    )):Name::STRING AS MANAGER,
    -- 保留筛选后的JSON数组作为CONTENTS
    ARRAY_FILTER(
        company[0]:company_data,
        x -> x:Position::STRING = 'Manager'
    ) AS CONTENTS
FROM
    poorly_imagined_table;

原方法失效原因说明 #

• 索引[0]取值：仅能匹配数组首位元素，无法适应Position位置变化的场景，通用性差。
• 字段内WHERE筛选写法：不符合Snowflake的JSON路径语法规则，平台不支持这种嵌套筛选写法，必须通过数组展开或数组函数实现元素筛选。

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