如何在SQL生成的JSON根节点前添加dbName字段

问题背景 #

现有数据库表及初始化数据：

create table samplejson (recordId varchar(10), docNumber varchar(10))
insert into samplejson values ('1', '555555')
insert into samplejson values ('2', '777777')

需要生成包含根节点dbName字段的JSON，格式要求如下：

{ "dbName": "test123",
  "documents": [
    {
      "json": {
        "RecordId": "1",
        "docNumber": "555555"
      }
    },
    {
      "json": {
        "RecordId": "2",
        "docNumber": "777777"
      }
    }
  ]
}

目前已能通过以下SQL生成正确的documents结构，但无法在根节点添加dbName字段：

select recordId [json.RecordId],
docNumber [json.docNumber]
from samplejson
for json path, root('documents')

解决方法 #

方法1：使用JSON_MODIFY拼接字段 #

先生成包含documents的JSON字符串，再通过JSON_MODIFY函数在根节点插入dbName字段：

SELECT JSON_MODIFY(
    (SELECT recordId [json.RecordId], docNumber [json.docNumber] FROM samplejson FOR JSON PATH, ROOT('documents')),
    '$.dbName',
    'test123'
) AS final_json;

方法2：子查询构造完整JSON结构 #

直接构造包含dbName和documents的完整结构，documents部分通过子查询生成，最后用WITHOUT_ARRAY_WRAPPER去掉默认外层数组：

SELECT 
    'test123' AS dbName,
    (SELECT recordId [json.RecordId], docNumber [json.docNumber] FROM samplejson FOR JSON PATH) AS documents
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

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