使用OPENJSON将SQL Server中的JSON转换为逗号分隔的列表

Question

在SQL Server 2016中，我试图转换以下JSON:

DECLARE @json NVARCHAR(MAX);
SET @json = 
N' {
"date": "2021-12-31",
"distributor_name": "Test",
"movies": [
{
"category_id": 3,
"name": "Dune",
"budget": 165,
"release_date": "2021-09-03",
"location": [
{
"location_type": 1, 
"location_code": "US"
},
{
"location_type": 2,
"location_code": "CA"
},
{
"location_type": 2,
"location_code": "UK"
}
]
},
{
"category_id": 2,
"name": "No Time to Die",
"budget": 250,
"release_date": "2021-09-28",

"location": [
{
"location_type": 1, 
"location_code": "US"
},
{
"location_type": 1, 
"location_code": "UK"
}
]
}
]
}
';

为:

预算国家tbody> <<tr>2

category_id	name	release_date	发行
3	沙丘	165	2021-09-03	我们	钙、英国
	没有时间去死	250	2021-09-28	美国、英国	空

您可以在子查询中再次使用OPENJSON来拆分内部location数组。

对于SQL Server 2016，需要使用FOR XML进行聚合。

DECLARE @sep varchar(10) = ',';
SELECT
j.category_id,
j.name,
j.budget,
j.release_date,
country = STUFF((
SELECT
@sep + location_code
FROM OPENJSON(j.location)
WITH (
location_type int,
location_code char(2)
) j2
WHERE j2.location_type = 1
FOR XML PATH(''), TYPE
).value('text()[1]','varchar(max)'), 1, LEN(@sep), ''),
distribution = STUFF((
SELECT
@sep + location_code
FROM OPENJSON(j.location)
WITH (
location_type int,
location_code char(2)
) j2
WHERE j2.location_type = 2
FOR XML PATH(''), TYPE
).value('text()[1]','varchar(max)'), 1, LEN(@sep), '')
FROM OPENJSON(@json, '$.movies')
WITH (
category_id int,
name nvarchar(200),
budget int,
release_date datetime,
location nvarchar(max) AS JSON
) j;

以后的版本可以更简单地使用STRING_AGG:

SELECT
j.category_id,
j.name,
j.budget,
j.release_date,
country = (
SELECT STRING_AGG(j2.location_code, ',')
FROM OPENJSON(j.location)
WITH (
location_type int,
location_code char(2)
) j2
WHERE j2.location_type = 1
),
distribution = (
SELECT STRING_AGG(j2.location_code, ',')
FROM OPENJSON(j.location)
WITH (
location_type int,
location_code char(2)
) j2
WHERE j2.location_type = 2
)
FROM OPENJSON(@json, '$.movies')
WITH (
category_id int,
name nvarchar(200),
budget int,
release_date datetime,
location nvarchar(max) AS JSON
) j;

,db&lt的在小提琴