使用FOR JSON PATH创建嵌套的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

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:

{

"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"}}}

]

}

但是,我希望将pets数据作为所有者数据中的数组存储:

{

"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 JSON PATH创建嵌套的JSON数组 的全部内容, 来源链接: utcz.com/qa/403672.html

回到顶部