如何在Redshift中使用PartiQL查询含嵌套对象数组的JSON数据？

现有JSON数据如下：

{
  "id": 1,
  "sets": [
    {
      "values": [
        {
          "value": 1
        },
        {
          "value": 2
        }
      ]
    },
    {
      "values": [
        {
          "value": 5
        },
        {
          "value": 6
        }
      ]
    }
  ]
}

对应Redshift表名为X，尝试执行以下PartiQL查询：

SELECT x.id, v.value 
FROM X as x,
  x.sets as sets,
  sets.values as v

期望得到结果（注：原期望结果中id为2属于笔误，正确结果id应为1）：

id, value
1, 1
1, 2
1, 5
1, 6

但当sets和values包含多个对象时，查询报错：column 'id' had 0 remaining values but expected 2，需要明确Redshift中查询此类嵌套数组结构的正确PartiQL写法。

在Redshift中处理多层嵌套数组时，必须通过UNNEST显式展开每一层数组，才能保证外层标量字段与内层数组元素正确关联。以下是两种可行的写法：

写法一：使用CROSS JOIN UNNEST #

SELECT x.id, v.value
FROM X x
CROSS JOIN UNNEST(x.sets) AS sets
CROSS JOIN UNNEST(sets.values) AS v;

写法二：使用隐式JOIN的UNNEST语法 #

SELECT x.id, v.value
FROM X x,
     UNNEST(x.sets) AS sets,
     UNNEST(sets.values) AS v;

错误原因 #

原查询的语法仅适用于单层数组或每层数组只有一个元素的场景。当存在多层多元素数组时，Redshift无法自动匹配外层标量字段（如id）与内层数组的多个元素，导致元素数量不匹配的报错。通过UNNEST逐层展开数组，能生成正确的笛卡尔积结果，确保每个数组元素都能关联到外层的id字段。

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