如何在T-SQL中解析嵌套JSON并生成指定表格结果

问题背景 #

现有如下嵌套JSON数据，尝试用JSON_QUERY只能提取子JSON对象，无法拆分成目标格式的表格：

declare @wjson2 nvarchar(max)
set @wjson2=N'{
  "requestBody": {
       "neW_ITEM_VENDORMAT": {
      "1": "1560993",
      "2": "1109365",
      "3": "1472360",
      "4": "739861",
      "5": "966391",
      "6": "127652",
      "7": "1082130",
      "8": "1560999",
      "9": "925470",
      "10": "1160384",
      "11": "164386",
      "12": "1118238",
      "13": "652887",
      "14": "75311",
      "15": "347861",
      "16": "1563788",
      "17": "1025063",
      "18": "131099",
      "19": "1561856"

    },

    "neW_ITEM_EXT_PRODUCT_ID": {
      "1": "1560993",
      "2": "1109365",
      "3": "1472360",
      "4": "739861",
      "5": "966391",
      "6": "127652",
      "7": "1082130",
      "8": "1560999",
      "9": "925470",
      "10": "1160384",
      "11": "164386",
      "12": "1118238",
      "13": "652887",
      "14": "75311",
      "15": "347861",
      "16": "1563788",
      "17": "1025063",
      "18": "131099",
      "19": "1561856"
    },
     "neW_ITEM_EXT_QUOTE_ID": {
      "1": "161389036",
      "2": "161389036",
      "3": "161389036",
      "4": "161389036",
      "5": "161389036",
      "6": "161389036",
      "7": "161389036",
      "8": "161389036",
      "9": "161389036",
      "10": "161389036",
      "11": "161389036",
      "12": "161389036",
      "13": "161389036",
      "14": "161389036",
      "15": "161389036",
      "16": "161389036",
      "17": "161389036",
      "18": "161389036",
      "19": "161389036"
    }
}
}'

-- 原尝试代码，仅能提取子JSON对象
SELECT
  JSON_QUERY(@wjson2, '$.requestBody.neW_ITEM_VENDORMAT') as neW_ITEM_VENDORMAT,
  JSON_QUERY(@wjson2, '$.requestBody.neW_ITEM_EXT_PRODUCT_ID') as neW_ITEM_EXT_PRODUCT_ID,
  JSON_QUERY(@wjson2, '$.requestBody.neW_ITEM_EXT_QUOTE_ID') as neW_ITEM_EXT_QUOTE_ID

期望得到的结构化表格：

解决方案 #

使用OPENJSON解析每个子对象，通过共同的id键关联三个数据集，最终拼接成目标表格：

declare @wjson2 nvarchar(max)
set @wjson2=N'{
  "requestBody": {
       "neW_ITEM_VENDORMAT": {
      "1": "1560993",
      "2": "1109365",
      "3": "1472360",
      "4": "739861",
      "5": "966391",
      "6": "127652",
      "7": "1082130",
      "8": "1560999",
      "9": "925470",
      "10": "1160384",
      "11": "164386",
      "12": "1118238",
      "13": "652887",
      "14": "75311",
      "15": "347861",
      "16": "1563788",
      "17": "1025063",
      "18": "131099",
      "19": "1561856"

    },

    "neW_ITEM_EXT_PRODUCT_ID": {
      "1": "1560993",
      "2": "1109365",
      "3": "1472360",
      "4": "739861",
      "5": "966391",
      "6": "127652",
      "7": "1082130",
      "8": "1560999",
      "9": "925470",
      "10": "1160384",
      "11": "164386",
      "12": "1118238",
      "13": "652887",
      "14": "75311",
      "15": "347861",
      "16": "1563788",
      "17": "1025063",
      "18": "131099",
      "19": "1561856"
    },
     "neW_ITEM_EXT_QUOTE_ID": {
      "1": "161389036",
      "2": "161389036",
      "3": "161389036",
      "4": "161389036",
      "5": "161389036",
      "6": "161389036",
      "7": "161389036",
      "8": "161389036",
      "9": "161389036",
      "10": "161389036",
      "11": "161389036",
      "12": "161389036",
      "13": "161389036",
      "14": "161389036",
      "15": "161389036",
      "16": "161389036",
      "17": "161389036",
      "18": "161389036",
      "19": "161389036"
    }
}
}'

SELECT
    v.id,
    v.neW_ITEM_VENDORMAT,
    p.neW_ITEM_EXT_PRODUCT_ID,
    q.neW_ITEM_EXT_QUOTE_ID
FROM
    -- 解析第一个子对象，获取id和对应值
    OPENJSON(JSON_QUERY(@wjson2, '$.requestBody.neW_ITEM_VENDORMAT'))
    WITH (
        id varchar(10) '$',
        neW_ITEM_VENDORMAT varchar(20) '$.value'
    ) v
LEFT JOIN
    -- 解析第二个子对象
    OPENJSON(JSON_QUERY(@wjson2, '$.requestBody.neW_ITEM_EXT_PRODUCT_ID'))
    WITH (
        id varchar(10) '$',
        neW_ITEM_EXT_PRODUCT_ID varchar(20) '$.value'
    ) p ON v.id = p.id
LEFT JOIN
    -- 解析第三个子对象
    OPENJSON(JSON_QUERY(@wjson2, '$.requestBody.neW_ITEM_EXT_QUOTE_ID'))
    WITH (
        id varchar(10) '$',
        neW_ITEM_EXT_QUOTE_ID varchar(20) '$.value'
    ) q ON v.id = q.id
ORDER BY
    CAST(v.id AS INT)

代码说明 #

1. OPENJSON用于将JSON对象拆分成键值对行数据，通过WITH子句定义输出列的结构，'$'表示键（即id），'$.value'表示对应的值。
2. 以第一个对象的id为基准，左连接另外两个对象的解析结果，确保所有id都能被覆盖。
3. 最后通过CAST(v.id AS INT)排序，保证结果按id从小到大排列。

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