Snowflake中查询指定条件JSON数组无结果的问题排查

问题根源 #

你的查询未返回数据，核心原因是IAEBREAKDOWNS:breakdownScope1and2是JSON数组类型，直接通过IAEBREAKDOWNS:breakdownScope1and2.breakdownBy这种方式访问时，Snowflake不会遍历数组内的元素做匹配，而是把整个数组当作单个对象读取属性，自然无法命中目标条件。

正确查询语句 #

要筛选出breakdownBy = 'DQS'且viewBy = 'Account'的breakdown数据，需要先用FLATTEN函数展开数组，再对单个元素做过滤：

SELECT flattened.value:breakdown
FROM ACCOUNTS_DATA,
     LATERAL FLATTEN(INPUT => IAEBREAKDOWNS:breakdownScope1and2) AS flattened
WHERE variationid = '063de708-a90a-42e2-849d-89d6d56cc00e'
  AND IAEBREAKDOWNS IS NOT NULL
  AND flattened.value:breakdownBy = 'DQS'
  AND flattened.value:viewBy = 'Account'

关键说明 #

• LATERAL FLATTEN：将breakdownScope1and2数组拆分为多行，每行对应数组里的一个JSON对象，通过flattened.value可以访问单个对象的属性。
• 过滤条件改为针对展开后的单个数组元素判断，这样就能精准匹配到符合条件的对象，提取对应的breakdown数据。

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