Snowflake嵌套JSON查询返回空行，请求排查问题原因

1. 三级数组展开的路径错误 #

你当前对LEVEL3ARRAY使用的FLATTEN输入是LEVEL2ARRAY.VALUE:breakdown，但从提供的JSON数据可以看到，三级明细数据实际存放在LEVEL2ARRAY.VALUE:level2Breakdown:breakdown路径下，而非直接的breakdown字段。这导致FLATTEN找不到可展开的数组，直接过滤掉了本该返回的行。

2. 未使用OUTER LATERAL FLATTEN导致缺失数据 #

由于JSON中部分条目level2Breakdown为null（没有三级数据），普通的LATERAL FLATTEN是内连接逻辑——当输入的数组不存在或为null时，会直接剔除该行。而你的场景需要兼容“存在/不存在level2breakdown”的情况，必须用OUTER LATERAL FLATTEN来保留无三级数据的行。

3. 冗余的字符串匹配条件限制结果集 #

WHERE子句中的IAECARBONBASELINESBREAKDOWN LIKE '%level2Breakdown%'是基于JSON字符串的模糊匹配，它会过滤掉完全没有level2Breakdown字段的JSON条目，违背了你“JSON可能包含或不包含level2breakdown”的需求，建议移除。

修正后的查询语句 #

SELECT 
  ROUND(ZEROIFNULL(LEVEL1ARRAY.VALUE:weightedAverage),1) AS weightedAverage,  
  REPLACE(LEVEL2ARRAY.VALUE:name,'"','') AS name,
  LEVEL2ARRAY.VALUE:percent AS percent,
  LEVEL2ARRAY.VALUE:total AS total,
  COALESCE(LEVEL2ARRAY.VALUE:level2Breakdown:breakdownBy,'') AS level2Breakdown,
  LEVEL3ARRAY.VALUE:name AS level2BreakdownName,
  LEVEL3ARRAY.VALUE:total AS level2BreakdownTotal,
  LEVEL3ARRAY.VALUE:percent AS level2BreakdownPercent
FROM
  ESG_ACCOUNTS_DATA,
  LATERAL FLATTEN (INPUT => PARSE_JSON(IAECARBONBASELINESBREAKDOWN):breakdownScope1and2) LEVEL1ARRAY,
  LATERAL FLATTEN (INPUT => LEVEL1ARRAY.VALUE:breakdown) LEVEL2ARRAY,
  OUTER LATERAL FLATTEN (INPUT => LEVEL2ARRAY.VALUE:level2Breakdown:breakdown) LEVEL3ARRAY,
WHERE
  variationid = '99e00658-a85e-419d-89f1-751f1dce1aba'
  AND IAECARBONBASELINESBREAKDOWN IS NOT NULL
  AND UPPER(LEVEL1ARRAY.VALUE:viewBy) = UPPER('account') 
  AND UPPER(LEVEL1ARRAY.VALUE:breakdownBy) = UPPER('yearofEmissions');

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