如何编写Snowflake查询从JSON数组中筛选符合条件的数据？

你的查询问题分析 #

• array_contains用法错误：array_contains要求第一个参数匹配数组内的完整元素结构，你传入的是单个字符串'Amazon'，但line_items的元素是JSON对象，同时大小写不匹配（数据里是AMAZON/EBAY，你写的是Amazon），所以完全无法匹配。
• FLATTEN未加筛选条件：第二个查询仅展开了数组，但没有添加sourceSupplier的过滤逻辑，导致返回所有原记录，甚至会重复输出同一条原记录。
• FLATTEN输入路径错误：第三个查询里input => json_table.json_column:sourceSupplier是错误的，sourceSupplier是数组内对象的属性，并非顶层数组，这个路径取不到有效数组，自然出不来正确结果。
• FLATTEN参数错误+无筛选：第四个查询里flatten(S.line_items,'root')的第二个参数用法错误，且同样没有添加过滤条件，无法筛选出目标记录。

正确查询方法 #

方法1：用array_any（无需展开数组，推荐）

通过lambda函数检查数组中是否存在满足条件的元素，注意大小写匹配：

SELECT top 100 line_items
FROM json_table.json_column
WHERE array_any(line_items, (x) => x:sourceSupplier IN ('AMAZON', 'EBAY'))
ORDER BY _TS DESC;

如果需要忽略大小写，可改用ILIKE：

SELECT top 100 line_items
FROM json_table.json_column
WHERE array_any(line_items, (x) => x:sourceSupplier::string ILIKE ANY ('amazon', 'ebay'))
ORDER BY _TS DESC;

方法2：用LATERAL FLATTEN+EXISTS子查询

展开数组后筛选符合条件的原记录，避免重复输出：

SELECT top 100 j.line_items
FROM json_table.json_column j
WHERE EXISTS (
    SELECT 1
    FROM LATERAL FLATTEN(input => j.line_items) f
    WHERE f.value:sourceSupplier IN ('AMAZON', 'EBAY')
)
ORDER BY _TS DESC;

也可以展开后去重：

SELECT DISTINCT top 100 j.line_items
FROM json_table.json_column j
LATERAL FLATTEN(input => j.line_items) f
WHERE f.value:sourceSupplier IN ('AMAZON', 'EBAY')
ORDER BY _TS DESC;

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