Snowflake嵌套JSON对象与数组查询结果不符，求正确SQL写法

你的问题出在没利用数组的位置索引关联col中的ST值和row.data里的数值，导致所有ST和所有data值做了全匹配，产生了笛卡尔积。

要得到预期结果，核心是让col里的第N个ST值，对应每个row中data数组的第N个元素。可以通过FLATTEN函数的INDEX参数保留数组元素的位置索引，再通过索引关联数据。

正确的SQL查询 #

SELECT 
    t.$1:dim[0]::string AS pov1,
    t.$1:dim[1]::string AS pov2,
    t.$1:dim[2]::string AS pov3,
    t.$1:dim[3]::string AS pov4,
    s.value::string AS st,
    a.value:head[0]::string AS ac,
    am.value::string AS at
FROM my_table t
-- 展开col数组，保留每个ST的位置索引
, TABLE(FLATTEN(input => ARRAY_FLATTEN(t.$1:col), INDEX => true)) s
-- 展开row数组
, LATERAL FLATTEN(input => t.$1:row) a
-- 展开每个row里的data数组，保留位置索引
, TABLE(FLATTEN(input => a.value:data, INDEX => true)) am
-- 通过索引关联ST和对应的data元素
WHERE s.index = am.index;

关键改动说明 #

• 在FLATTEN处理col和data时，添加INDEX => true参数，生成index列记录元素在原数组中的位置（从0开始）。
• 用WHERE s.index = am.index让每个ST值只匹配对应位置的data数值，彻底消除笛卡尔积。
• 修正a.value:head的取值：head是数组（比如["AC1"]），需用head[0]取出具体值，避免返回数组格式。

执行这个查询后，就能得到你预期的9条结果，每个ST对应正确的AC和数值。

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