在JSON列中选择并显示数组的命名元素

Question

在Azure SQL中，我有一个表"violation"和一个JSON列"course_json"它包含一个数组。例如:

[{
"course_int": "1465",
"course_key": "LEND1254",
"course_name": "Mortgage Servicing Introduction",
"test_int": "0"
}, {
"course_int": "1464",
"course_key": "LEND1211",
"course_name": "Mortgage Servicing Transfer",
"test_int": "0"
}]

我想在违规表中选择行，并显示表的列和"course_key";:

LEND12654,LEND1211

如果总是有固定数量的course_key，我可以使用:

select person_id,event_date, JSON_VALUE(course_json, '$[0].course_key') + ',' + JSON_VALUE(course_json, '$[1].course_key')  from violation

但他们不是固定的…可能有一个、两个、十个……我永远不会知道。

那么，有可能遍历所有的course_keys并以逗号分隔的格式显示它们吗?

Answer 1

代替JSON_VALUE，使用OPENJSON获取所有课程，使用STRING_AGG构建course_key分隔列表。

SELECT
person_id
, event_date
, (SELECT STRING_AGG(course_key,',')
FROM OPENJSON(course_json)
WITH (
course_key nvarchar(MAX) '$.course_key'
)) AS course_key
FROM dbo.violation;

course_keyLEND1254, LEND1211