如何在SQL Server导出的JSON每条记录顶层添加imageUrl字段

步骤1：提取每个文档对应的图片路径 #

先通过CTE从names表中筛选出每个doc_id对应的图片路径，这里假设路径以\\开头，用来区分姓名类记录：

WITH DocumentImages AS (
    SELECT 
        doc_id,
        -- 根据实际存储位置调整字段，这里兼容路径在lastname或firstname字段的情况
        CASE
            WHEN lastname LIKE '\\%' THEN lastname
            WHEN firstname LIKE '\\%' THEN firstname
        END AS imageUrl
    FROM names
    WHERE lastname LIKE '\\%' OR firstname LIKE '\\%'
)

步骤2：构造包含ImageUrl的目标JSON结构 #

将图片路径与原文档信息平级放置，同时在姓名子查询中排除路径记录，避免干扰结果：

SELECT
    (SELECT
         di.imageUrl,
         (SELECT
              d.doc_id AS [doc_id],
              d.recordId AS [recordId],
              d.filingDate AS [filing_date],
              (SELECT
                   lastname AS nameLast,
                   firstname AS nameFirst
               FROM names n
               WHERE n.doc_id = d.doc_id
                 -- 过滤掉路径记录，只保留姓名数据
                 AND NOT (lastname LIKE '\\%' OR firstname LIKE '\\%')
               FOR JSON PATH) AS [blockNames]
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS [json]
     FROM docs d
     JOIN DocumentImages di ON d.doc_id = di.doc_id
     FOR JSON PATH) AS docs
FOR JSON PATH

关键说明 #

• WITHOUT_ARRAY_WRAPPER用于让内部文档信息生成单个对象而非数组，确保json字段是一个结构化对象。
• 过滤条件确保blockNames只包含姓名记录，不会混入图片路径。
• 如果图片路径在names表中有固定专属字段（比如单独的imageUrl字段），可直接替换CTE中的字段引用，简化逻辑。

示例输出结构 #

最终生成的JSON会符合以下格式：

[
    {
        "docs": [
            {
                "imageUrl": "\\\\Server1\\Share\\Image1.pdf",
                "json": {
                    "doc_id": "12345",
                    "recordId": "GUID4",
                    "filing_date": "2022-05-01T00:00:00",
                    "blockNames": [
                        {"nameLast": "SMITH", "nameFirst": "JOHN"},
                        {"nameLast": "SMITH", "nameFirst": "LISA"}
                    ]
                }
            },
            {
                "imageUrl": "\\\\Server1\\Share\\Image2.pdf",
                "json": {
                    "doc_id": "55555",
                    "recordId": "GUID5",
                    "filing_date": "2021-07-07T00:00:00",
                    "blockNames": [
                        {"nameLast": "JENKINS", "nameFirst": "MARSHA"},
                        {"nameLast": "JENKINS", "nameFirst": "BOB"}
                    ]
                }
            }
        ]
    }
]

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