将嵌套JSON规范化为多张SQL表的透视顺序问题求解

核心问题根源 #

你遇到的顺序混乱，本质是递归OPENJSON生成的键值对没有保留原JSON数组中单个Contact的归属标识——同一RowId下的多个Contact字段被打散后，无法区分哪些键值对属于同一个Contact，导致透视时字段错位。

方案1：直接解析为结构化表（推荐，高效无顺序问题） #

无需先转存为键值对表，直接通过OPENJSON的WITH子句一次性解析JSON数组为结构化行，自动保留每个Contact的字段关联关系。

假设dbo.app2023表结构为RowId INT, JsonData NVARCHAR(MAX)，JSON格式示例：

{
  "Contacts": [
    {"Name": "张三", "Phone": "13800138000", "Email": "zhangsan@example.com"},
    {"Name": "李四", "Phone": "13900139000", "Email": "lisi@example.com"}
  ]
}

执行以下SQL直接生成结构化的Contact表数据：

SELECT
  a.RowId,
  c.Name,
  c.Phone,
  c.Email
FROM dbo.app2023 a
CROSS APPLY OPENJSON(a.JsonData, '$.Contacts')
WITH (
  Name NVARCHAR(50) '$.Name',
  Phone NVARCHAR(20) '$.Phone',
  Email NVARCHAR(100) '$.Email'
) c;

此方法直接将每个Contact对象映射为一行，字段天然对应，完全避免透视操作和顺序问题。

方案2：保留索引后再透视（适配必须先存键值对的场景） #

如果业务需求要求先将JSON解析为键值对存入ParseContact2023，必须在解析阶段保留数组索引（即每个Contact在原JSON数组中的位置），后续透视时通过RowId+ContactIndex分组，确保同一Contact的字段归为一行。

步骤1：带索引解析键值对

INSERT INTO ParseContact2023 (RowId, ContactIndex, KeyName, KeyValue)
SELECT
  a.RowId,
  CAST([key] AS INT) AS ContactIndex, -- 数组索引，转INT方便排序
  j.KeyName,
  j.KeyValue
FROM dbo.app2023 a
CROSS APPLY OPENJSON(a.JsonData, '$.Contacts') -- 解析Contacts数组
CROSS APPLY (
  SELECT [key] AS KeyName, [value] AS KeyValue
  FROM OPENJSON([value]) -- 解析单个Contact对象的键值对
) j;

步骤2：按索引分组透视

SELECT
  RowId,
  ContactIndex,
  MAX(CASE WHEN KeyName = 'Name' THEN KeyValue END) AS Name,
  MAX(CASE WHEN KeyName = 'Phone' THEN KeyValue END) AS Phone,
  MAX(CASE WHEN KeyName = 'Email' THEN KeyValue END) AS Email
FROM ParseContact2023
GROUP BY RowId, ContactIndex
ORDER BY RowId, ContactIndex;

通过ContactIndex标识同一Contact的所有键值对，分组透视后就能保证字段顺序和归属正确。

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