SQL Server嵌套JSON数组提取：将JSON对象转为行数据

我的SQL Server数据库中有一个JSON列，每行包含多个被大括号包裹的嵌套对象（整个JSON为数组格式），需要将这些嵌套对象中的所有元素提取为行数据，提取后的列名与嵌套对象中的键名对应。

示例JSON数据：

[
    {
        "loanBalance":14442.72,
        "balancePeriod":"2022-02-28T00:00:00+02:00",
        "guaranteeBalance":11554.18
    },
    {
        "chargeId":"21330",
        "loanBalance":13359.71,
        "balancePeriod":"2022-03-31T00:00:00+03:00",
        "guaranteeBalance":10687.77,
        "guaranteeFeeAmount":22.41,
        "guaranteeFeeDueDate":"2022-04-24T00:00:00+03:00"
    },
    {
        "chargeId":"23466",
        "loanBalance":13223.71,
        "balancePeriod":"2022-04-30T00:00:00+03:00",
        "guaranteeBalance":10578.97,
        "guaranteeFeeAmount":20.74,
        "guaranteeFeeDueDate":"2022-06-02T00:00:00+03:00"
    }
]

原代码问题 #

之前的查询使用JSON_QUERY(JSON_COLUMN, '$[0]')仅提取了数组的第一个元素，导致只能得到第一行数据。

数据准备代码 #

DROP TABLE IF EXISTS #TEMP2;

CREATE TABLE #TEMP2 (
ID INT,
JSON_COLUMN VARCHAR(MAX)
);

INSERT INTO #TEMP2 VALUES (
1, N'[{"loanBalance": 100000.0, "balancePeriod": "2022-02-28T00:00:00+02:00", "guaranteeBalance": 75000.0}, {"chargeId": "21671", "loanBalance": 100000.0, "balancePeriod": "2022-03-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-05-09T00:00:00+03:00"}, {"chargeId": "23678", "loanBalance": 100000.0, "balancePeriod": "2022-04-30T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2022-06-03T00:00:00+03:00"}, {"chargeId": "26077", "loanBalance": 100000.0, "balancePeriod": "2022-05-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-08-06T00:00:00+03:00"}, {"chargeId": "26956", "loanBalance": 100000.0, "balancePeriod": "2022-06-30T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2022-08-12T00:00:00+03:00"}, {"chargeId": "32760", "loanBalance": 100000.0, "balancePeriod": "2022-07-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-11-20T00:00:00+02:00"}, {"chargeId": "33605", "loanBalance": 100000.0, "balancePeriod": "2022-08-31T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2022-12-01T00:00:00+02:00"}, {"chargeId": "36010", "loanBalance": 100000.0, "balancePeriod": "2022-09-30T00:00:00+03:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2023-01-15T00:00:00+02:00"}, {"chargeId": "37025", "loanBalance": 100000.0, "balancePeriod": "2022-10-31T00:00:00+02:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2023-02-10T00:00:00+02:00"}, {"chargeId": "37032", "loanBalance": 100000.0, "balancePeriod": "2022-11-30T00:00:00+02:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 137.5, "guaranteeFeeDueDate": "2023-02-10T00:00:00+02:00"}, {"chargeId": "37037", "loanBalance": 100000.0, "balancePeriod": "2022-12-31T00:00:00+02:00", "guaranteeBalance": 75000.0, "guaranteeFeeAmount": 142.08, "guaranteeFeeDueDate": "2023-02-10T00:00:00+02:00"}]'
);

INSERT INTO #TEMP2 VALUES (
2, N'[{"loanBalance": 14442.72, "balancePeriod": "2022-02-28T00:00:00+02:00", "guaranteeBalance": 11554.18}, {"chargeId": "21330", "loanBalance": 13359.71, "balancePeriod": "2022-03-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 22.41, "guaranteeFeeDueDate": "2022-04-24T00:00:00+03:00"}, {"chargeId": "23466", "loanBalance": 13223.71, "balancePeriod": "2022-04-30T00:00:00+03:00", "guaranteeBalance": 10578.97, "guaranteeFeeAmount": 20.74, "guaranteeFeeDueDate": "2022-06-02T00:00:00+03:00"}, {"chargeId": "26511", "loanBalance": 13359.71, "balancePeriod": "2022-05-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 21.43, "guaranteeFeeDueDate": "2022-08-11T00:00:00+03:00"}, {"chargeId": "31054", "loanBalance": 13359.71, "balancePeriod": "2022-06-30T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 20.84, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "31068", "loanBalance": 13359.71, "balancePeriod": "2022-07-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 21.54, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "31073", "loanBalance": 13359.71, "balancePeriod": "2022-08-31T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 21.54, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "31075", "loanBalance": 13359.71, "balancePeriod": "2022-09-30T00:00:00+03:00", "guaranteeBalance": 10687.77, "guaranteeFeeAmount": 20.84, "guaranteeFeeDueDate": "2022-10-17T00:00:00+03:00"}, {"chargeId": "37752", "loanBalance": "11903.33", "balancePeriod": "2022-10-31T00:00:00+02:00", "guaranteeBalance": "9522.66400000", "guaranteeFeeAmount": "23.8426", "guaranteeFeeDueDate": "2023-02-13T00:00:00+02:00"}]'
);

正确查询代码 #

直接对整个JSON数组使用OPENJSON，无需指定数组索引，即可遍历所有嵌套对象：

SELECT
    t.ID,
    elements.loanBalance,
    elements.balancePeriod,
    elements.guaranteeBalance,
    elements.chargeId,
    elements.guaranteeFeeAmount,
    elements.guaranteeFeeDueDate
FROM
    #TEMP2 t
CROSS APPLY
    OPENJSON(t.JSON_COLUMN) WITH (
        loanBalance DECIMAL(18,6),
        balancePeriod DATETIMEOFFSET,
        guaranteeBalance DECIMAL(18,6),
        chargeId NVARCHAR(100),
        guaranteeFeeAmount DECIMAL(18,6),
        guaranteeFeeDueDate DATETIMEOFFSET
    ) AS elements;

关键说明 #

1. 去掉数组索引限制：原代码中$[0]强制只取数组第一个元素，现在直接使用OPENJSON(t.JSON_COLUMN)解析整个数组，自动将每个JSON对象转为一行数据。
2. 关联原表数据：保留原表的ID列，方便后续关联原数据行和解析后的JSON对象行。
3. 优化数据类型：将原代码中统一的NVARCHAR类型改为更贴合业务的类型（如金额用DECIMAL，日期用DATETIMEOFFSET），提升数据准确性和性能。

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