使用FOR JSON PATH创建嵌套的JSON数组 [英] Create nested JSON arrays using FOR JSON PATH

问题描述

我需要从查询创建一个JSON输出，该查询使用两个表之间具有一对多关系的内部联接.
我希望将辅助表的值作为主表的数组属性嵌套.

I need to create a JSON output from a query that uses inner join between two tables with a one to many relationship.
I would like the values of the secondary table to be nested as array properties of the primary table.

请考虑以下示例:

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
    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')

然后查询:

DECLARE @Result as varchar(max)
SET @Result =
(
SELECT  person_id as [person.id],
        person_name as [person.name],
        pet_id as [person.pet.id],
        pet_name as [person.pet.name]
FROM @Persons 
JOIN @Pets ON person_id = pet_owner
FOR JSON PATH, ROOT('pet owners')
)

PRINT @Result

这将打印以下JSON:

This will print the following JSON:

{
    "pet owners":
    [
    {"person":{"id":2,"name":"Jack","pet":{"id":4,"name":"Bug"}}},
    {"person":{"id":2,"name":"Jack","pet":{"id":5,"name":"Feature"}}},
    {"person":{"id":3,"name":"Jill","pet":{"id":6,"name":"Fiend"}}}
    ]
}

但是，我希望宠物数据作为所有者数据中的数组:

However, I would like to have the pets data as arrays inside the owners data:

{
    "pet owners":
    [
        {
            "person":
            {
                "id":2,"name":"Jack","pet":
                [
                    {"id":4,"name":"Bug"},
                    {"id":5,"name":"Feature"}
                ]
            }
        },
        {
            "person":
            {
                "id":3,"name":"Jill","pet":
                {"id":6,"name":"Fiend"}
            }
        }
    ]
}

我该怎么做?

推荐答案

您可以使用以下查询:

SELECT pr.person_id AS [person.id], pr.person_name AS [person.name],
    (
        SELECT pt.pet_id AS id, pt.pet_name AS name 
        FROM @Pets pt WHERE pt.pet_owner=pr.person_id 
        FOR JSON PATH
    ) AS [person.pet]
FROM @Persons pr 
FOR JSON PATH, ROOT('pet owners')

有关更多信息，请参见

For more information, see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/

这篇关于使用FOR JSON PATH创建嵌套的JSON数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！