SQL Server 2019嵌套JSON查询：关联页面GUID与问题响应列表

核心思路 #

处理嵌套JSON数组时，需通过两层OPENJSON+APPLY关联，外层解析页面数组获取页面GUID，内层解析每个页面下的问题响应列表，确保页面GUID与对应问题/响应一一绑定。

示例表结构 #

假设存储JSON的表定义如下：

CREATE TABLE DocumentData (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Content JSON NOT NULL
);

示例JSON数据 #

插入包含多页面、多问题响应的测试数据：

INSERT INTO DocumentData (Content)
VALUES (N'{
    "Pages": [
        {
            "PageGuid": "A1B2C3D4-1234-5678-90AB-CDEF01234567",
            "QuestionResponses": [
                {
                    "Question": "什么是SQL Server？",
                    "Response": "微软推出的关系型数据库管理系统"
                },
                {
                    "Question": "JSON数据如何存储？",
                    "Response": "可使用JSON类型列或NVARCHAR列存储"
                }
            ]
        },
        {
            "PageGuid": "X9Y8Z7W6-4321-8765-09BA-FEDC76543210",
            "QuestionResponses": [
                {
                    "Question": "OPENJSON是什么？",
                    "Response": "SQL Server中用于解析JSON的函数"
                }
            ]
        }
    ]
}');

正确查询语句 #

SELECT 
    PageData.PageGuid,
    QnA.Question,
    QnA.Response
FROM 
    DocumentData
CROSS APPLY 
    OPENJSON(Content, '$.Pages')
    WITH (
        PageGuid UNIQUEIDENTIFIER '$.PageGuid',
        QuestionResponses NVARCHAR(MAX) '$.QuestionResponses' AS JSON
    ) AS PageData
CROSS APPLY 
    OPENJSON(PageData.QuestionResponses)
    WITH (
        Question NVARCHAR(MAX) '$.Question',
        Response NVARCHAR(MAX) '$.Response'
    ) AS QnA;

关键说明 #

• 外层OPENJSON指定路径$.Pages解析页面数组，同时将QuestionResponses标记为AS JSON，确保内层能正确识别并解析该子数组
• 使用CROSS APPLY关联外层页面与内层问题响应，每条问题记录都会携带所属页面的GUID
• 若需保留无问题响应的页面记录，可将CROSS APPLY替换为OUTER APPLY

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