SQL Server中合并嵌套JSON查询并关联匹配字段的需求

假设你的JSON数据结构类似如下（可根据实际结构调整解析路径）：

{
  "Models": [
    {
      "x": "Model1",
      "y": "AttrY1",
      "z": "AttrZ1",
      "Energy": {
        "A": ["E_A_001", "E_A_002"],
        "B": ["E_B_001"]
      }
    },
    {
      "x": "Model2",
      "y": "AttrY2",
      "z": "AttrZ2",
      "Energy": {
        "A": ["E_A_003"],
        "B": ["E_B_002", "E_B_003"]
      }
    }
  ]
}

需求一：匹配Energy-A、Energy-B列对应Model的x、y、z字段 #

通过OPENJSON解析Model层级，再用OUTER APPLY展开Energy下的A、B子数组，实现字段一一对应：

DECLARE @json NVARCHAR(MAX) = '你的JSON内容';

SELECT 
    m.x,
    m.y,
    m.z,
    eA.value AS Energy_A,
    eB.value AS Energy_B
FROM 
    OPENJSON(@json, '$.Models')
    WITH (
        x VARCHAR(50),
        y VARCHAR(50),
        z VARCHAR(50),
        Energy NVARCHAR(MAX) AS JSON -- 将Energy标记为JSON类型以便后续解析
    ) AS m
OUTER APPLY 
    OPENJSON(m.Energy, '$.A') AS eA -- 展开Energy下的A数组
OUTER APPLY 
    OPENJSON(m.Energy, '$.B') AS eB; -- 展开Energy下的B数组

需求二：将Energy子数组合并为分号分隔的单列 #

使用STRING_AGG函数对展开的数组元素分组拼接，对应Model的x、y、z字段：

DECLARE @json NVARCHAR(MAX) = '你的JSON内容';

SELECT 
    m.x,
    m.y,
    m.z,
    COALESCE(STRING_AGG(eA.value, ';'), '') AS Energy_A_Combined, -- 空数组时返回空字符串
    COALESCE(STRING_AGG(eB.value, ';'), '') AS Energy_B_Combined
FROM 
    OPENJSON(@json, '$.Models')
    WITH (
        x VARCHAR(50),
        y VARCHAR(50),
        z VARCHAR(50),
        Energy NVARCHAR(MAX) AS JSON
    ) AS m
OUTER APPLY 
    OPENJSON(m.Energy, '$.A') AS eA
OUTER APPLY 
    OPENJSON(m.Energy, '$.B') AS eB
GROUP BY 
    m.x, m.y, m.z; -- 按Model的唯一标识分组拼接

整合两种输出到单个查询 #

通过CTE（公共表表达式）先获取基础展开数据，再用窗口函数实现两种输出的整合：

DECLARE @json NVARCHAR(MAX) = '你的JSON内容';

WITH ModelEnergyCTE AS (
    SELECT 
        m.x,
        m.y,
        m.z,
        eA.value AS Energy_A,
        eB.value AS Energy_B,
        -- 窗口函数在不分组的情况下生成合并后的字符串
        STRING_AGG(eA.value, ';') OVER (PARTITION BY m.x, m.y, m.z) AS Energy_A_Combined,
        STRING_AGG(eB.value, ';') OVER (PARTITION BY m.x, m.y, m.z) AS Energy_B_Combined
    FROM 
        OPENJSON(@json, '$.Models')
        WITH (
            x VARCHAR(50),
            y VARCHAR(50),
            z VARCHAR(50),
            Energy NVARCHAR(MAX) AS JSON
        ) AS m
    OUTER APPLY 
        OPENJSON(m.Energy, '$.A') AS eA
    OUTER APPLY 
        OPENJSON(m.Energy, '$.B') AS eB
)
SELECT DISTINCT -- 去重避免重复行
    x, y, z,
    Energy_A, Energy_B,
    Energy_A_Combined, Energy_B_Combined
FROM ModelEnergyCTE;

注意事项 #

• 若你的JSON路径与示例不同，需调整OPENJSON的第二个参数（如$.Models、$.A）以匹配实际结构。
• STRING_AGG函数仅支持SQL Server 2017及以上版本，若使用更低版本，可通过FOR XML PATH的方式实现字符串拼接。

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