MS SQL使用OPENJSON解析平行JSON数组按索引匹配关联的问题

你现有写法产生笛卡尔积的核心问题是，两次CROSS APPLY OPENJSON()拆分平行数组时，没有按数组索引做关联。
OPENJSON解析JSON数组时会默认返回三列：

• key：数组元素从0开始的位置索引
• value：元素实际值
• type：元素类型标记
  用两个数组解析结果的key字段做等值匹配，就能把同位置的元素对应到同一行，完全符合预期输出。

DECLARE @json NVARCHAR(MAX) = N'[
   {
      "id":"1121131",
      "idGroups":[
         "123",
         "X999"
      ],
      "idGroupNames":[
         "Neutral",
         "Service"
      ]
   },
   {
      "id":"2233154",
      "idGroups":[
         "654321"
      ],
      "idGroupNames":[
         "Position"
      ]
   }
]'

SELECT 
    base.id,
    groups.idGroup,
    groupNames.idGroupName
FROM OPENJSON (@json)
WITH (
    id INT 'strict $.id',
    idGroups NVARCHAR(MAX) '$.idGroups' AS JSON,
    idGroupNames NVARCHAR(MAX) '$.idGroupNames' AS JSON
) base
CROSS APPLY OPENJSON(idGroups)
WITH (
    idGroup VARCHAR(500) '$',
    idx INT '$.key'
) groups
CROSS APPLY OPENJSON(idGroupNames)
WITH (
    idGroupName VARCHAR(500) '$',
    idx INT '$.key'
) groupNames
WHERE groups.idx = groupNames.idx

• 解析两个数组时，通过WITH子句把内置的key字段映射为idx索引字段，拿到每个元素在数组里的位置
• 最后加WHERE条件限定只有两个数组索引相等的行才匹配，从根源避免无关联的笛卡尔积
• 如果遇到两个数组长度不一致的场景，该写法默认只返回两个数组都存在对应索引的匹配行；如果需要保留某一侧无匹配的行，可以把第二个CROSS APPLY替换为OUTER APPLY调整关联逻辑。

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