sql-server - 输出包含多个数组的 JSON

Question

我正在尝试使用多个数组将查询输出为 JSON 格式，但无法使其正常工作。我遇到了这个 link ，但这不是我想要的输出。

以下是示例表:

DECLARE @Persons AS TABLE
(
    person_id int primary key,
    person_name varchar(20)
)

DECLARE @Pets AS TABLE
(
    pet_owner int, -- in real tables, this would be a foreign key to Person.person_id
    pet_id int  primary key,
    pet_name varchar(10)
)

INSERT INTO @Persons (person_id, person_name) VALUES
(2, 'Jack'),
(3, 'Jill')

INSERT INTO @Pets (pet_owner, pet_id, pet_name) VALUES
(2, 4, 'Bug'),
(2, 5, 'Feature'),
(3, 6, 'Fiend')

我想像下面这样生成 JSON 输出:

[{
    "2" : [{
            "pet_id" : 4,
            "name" : "Bug",
            },
            {
            "pet_id" : 5,
            "name" : "Feature",
            }
        ],
    "3" : [{
            "pet_id" : 6,
            "name" : "Fiend",
            }
        ]
}]

如果常规内置 FOR JSON Path选项不可行，我也采取任何技巧，如串联等。

Answer 1

我认为您不能仅使用 FOR JSON AUTO 生成带有可变键名的 JSON 输出。/FOR JSON PATH .

对于 SQL Server 2016+，一种可能的解决方案是使用 FOR JSON AUTO和 FOR XML PATH .

对于 SQL Server 2017+，您可以仅使用 JSON 函数生成预期输出，从 JSON 数组开始，包含一个空的 JSON 对象作为第一项。与 JSON_MODIFY()和 lax默认情况下，从 FOR JSON AUTO 生成的 JSON 输出作为键值插入，由 CONCAT('$[0]."', person_id, '"') 指定表达式(SQL Server 2017 支持使用表达式作为 path 参数的值)。说实话，JSON_QUERY()这里不需要，但通常，JSON_MODIFY()转义新值中的所有特殊字符。在这种情况下，您需要使用 FOR JSON 获取格式正确的 JSON , JSON_QUERY()或 JSON_MODIFY() ( JSON_QUERY(person_json, '$') 返回整个 JSON)。

表格:

DECLARE @Persons AS TABLE (
    person_id int primary key,
    person_name varchar(20)
)
DECLARE @Pets AS TABLE (
   pet_owner int, -- in real tables, this would be a foreign key to Person.person_id
   pet_id int  primary key,
   pet_name varchar(10)
)
INSERT INTO @Persons 
   (person_id, person_name) 
VALUES
   (2, 'Jack'),
   (3, 'Jill')
INSERT INTO @Pets 
   (pet_owner, pet_id, pet_name) 
VALUES
   (2, 4, 'Bug'),
   (2, 5, 'Feature'),
   (3, 6, 'Fiend')

语句(SQL Server 2016+):

SELECT CONCAT(
   '[{',
   STUFF((
      SELECT CONCAT(',"', person_id, '":', person_json)
      FROM @Persons p
      CROSS APPLY (
         SELECT pet_id, pet_name AS name
         FROM @Pets
         WHERE pet_owner = p.person_id
         FOR JSON AUTO
      ) j (person_json)
      FOR XML PATH('') 
      ), 1, 1, ''),
  '}]'
  )

语句(SQL Server 2017+):

DECLARE @json varchar(max) = '[{}]'

SELECT @json = JSON_MODIFY(
                  @json, 
                  CONCAT('$[0]."', person_id, '"'), 
                  --JSON_QUERY(person_json, '$')
                  person_json    
               )
FROM @Persons p
CROSS APPLY (
   SELECT pet_id, pet_name AS name
   FROM @Pets
   WHERE pet_owner = p.person_id
   FOR JSON AUTO
) j (person_json)

SELECT @json

结果:

[{"2":[{"pet_id":4,"name":"Bug"},{"pet_id":5,"name":"Feature"}],"3":[{"pet_id":6,"name":"Fiend"}]}]