SQL Server中生成含嵌套联系人JSON数组的行级JSON结果的实现方案

别担心，我之前也碰到过类似的需求，用SQL Server自带的FOR JSON PATH就能完美解决这个问题。下面是具体的实现方法，我会一步步给你解释清楚：

完整SQL语句 #

假设你的表名为YourTable，你可以用以下查询生成符合要求的JSON：

SELECT
    a,
    b,
    c,
    -- 构建contacts嵌套数组
    JSON_QUERY('[' +
        -- 生成第一个联系人的JSON对象
        JSON_QUERY((SELECT name1 AS name, phonenumber1 AS phone_num, address1 AS address FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) + ',' +
        -- 生成第二个联系人的JSON对象
        JSON_QUERY((SELECT name2 AS name, phonenumber2 AS phone_num, address2 AS address FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) +
    ']') AS contacts
FROM YourTable
-- 控制输出格式：如果需要每行返回单个JSON对象，加上WITHOUT_ARRAY_WRAPPER；如果要所有行在一个数组里则去掉
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

关键部分解释 #

1. 内层FOR JSON PATH, WITHOUT_ARRAY_WRAPPER：

   • 这部分用来生成单个联系人的JSON对象（比如{"name":"Alice","phone_num":"123","address":"NY"}），WITHOUT_ARRAY_WRAPPER确保不会给单个对象加上多余的数组方括号。

2. JSON_QUERY的作用：

   • 这个函数是核心，它告诉SQL Server我们传入的字符串是合法的JSON片段，不需要对它进行转义处理。如果不用JSON_QUERY，生成的contacts字段会变成转义后的字符串，而不是嵌套的JSON数组。

3. 外层FOR JSON PATH：

   • 把顶层的a、b、c字段和嵌套的contacts数组组合成最终的JSON结构。加上WITHOUT_ARRAY_WRAPPER会让每行输出一个独立的JSON对象；如果去掉这个参数，所有行的结果会被包裹在一个JSON数组里。

处理NULL值（可选） #

如果你的表中存在NULL值，SQL Server默认会忽略这些NULL字段。如果你希望保留它们（比如某个联系人的地址为空时显示"address": null），只需要在FOR JSON后面加上INCLUDE_NULL_VALUES参数即可，示例如下：

SELECT
    a,
    b,
    c,
    JSON_QUERY('[' +
        JSON_QUERY((SELECT name1 AS name, phonenumber1 AS phone_num, address1 AS address FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) + ',' +
        JSON_QUERY((SELECT name2 AS name, phonenumber2 AS phone_num, address2 AS address FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)) +
    ']') AS contacts
FROM YourTable
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES;

示例输出 #

假设你的表中有一行数据：
a=1, b=2, c=3, name1='Alice', address1='New York', phonenumber1='123-4567', name2='Bob', address2='Los Angeles', phonenumber2='987-6543'

执行查询后会得到如下JSON：

{
  "a": 1,
  "b": 2,
  "c": 3,
  "contacts": [
    {
      "name": "Alice",
      "phone_num": "123-4567",
      "address": "New York"
    },
    {
      "name": "Bob",
      "phone_num": "987-6543",
      "address": "Los Angeles"
    }
  ]
}

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