如何将JSON中Choices数据导入SQL Server指定结构表？

原始JSON数据 #

[
    {
        "id": 103001058774,
        "name": "status",
        "label": "Status",
        "description": "Ticket status",
        "choices": {
            "2": [
                "Open",
                "Open"
            ],
            "3": [
                "Pending",
                "Pending"
            ],
            "4": [
                "Resolved",
                "Resolved"
            ],
            "5": [
                "Closed",
                "Closed"
            ],
            "6": [
                "Waiting on Customer",
                "Awaiting your Reply"
            ],
            "7": [
                "Waiting on Third Party",
                "Being Processed"
            ],
            "8": [
                "Assigned",
                "Assigned"
            ]
        }
    }
]

目标SQL Server表结构 #

现有错误查询 #

你当前的查询仅返回整个Choices分支，无法拆分出单个id和对应标签：

DECLARE @jsonStatusesData NVARCHAR (MAX) = 'My JSON String'

SELECT id = JSON_QUERY(j.value, '$.choices')
FROM OPENJSON(@jsonStatusesData) AS j

正确的SQL实现 #

需要通过嵌套OPENJSON分层解析Choices节点的键值对：

1. 先解析外层JSON，定位到choices对象
2. 解析choices对象的每个键（作为id）和数组值
3. 从数组中提取对应位置的元素作为agent_label和customer_label

完整SQL语句：

DECLARE @jsonStatusesData NVARCHAR (MAX) = '[
    {
        "id": 103001058774,
        "name": "status",
        "label": "Status",
        "description": "Ticket status",
        "choices": {
            "2": ["Open", "Open"],
            "3": ["Pending", "Pending"],
            "4": ["Resolved", "Resolved"],
            "5": ["Closed", "Closed"],
            "6": ["Waiting on Customer", "Awaiting your Reply"],
            "7": ["Waiting on Third Party", "Being Processed"],
            "8": ["Assigned", "Assigned"]
        }
    }
]'

-- 提取数据并匹配目标表结构
SELECT
    CAST(c.[key] AS INT) AS id,
    JSON_VALUE(c.value, '$[0]') AS agent_label,
    JSON_VALUE(c.value, '$[1]') AS customer_label
FROM OPENJSON(@jsonStatusesData) AS j
CROSS APPLY OPENJSON(j.value, '$.choices') AS c

如果需要直接插入到目标表，可添加INSERT INTO语句：

INSERT INTO 你的目标表名 (id, agent_label, customer_label)
SELECT
    CAST(c.[key] AS INT) AS id,
    JSON_VALUE(c.value, '$[0]') AS agent_label,
    JSON_VALUE(c.value, '$[1]') AS customer_label
FROM OPENJSON(@jsonStatusesData) AS j
CROSS APPLY OPENJSON(j.value, '$.choices') AS c

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