In Azure SQL I have a table, "violation", with a JSON column, "course_json" that contains an array. An example is:

[{
    "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"
}]

I would like to select rows in the violation table and display columns of the table and the "course_key" as:

LEND12654,LEND1211

If there were always a fixed number of course_key's I could use:

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

But they aren't fixed... there may be one, two, ten... I'll never know.

So, is it possible to iterate through all the course_keys and display them all in a comma separated format?

CodePudding user response：

Instead of JSON_VALUE, use OPENJSON to get all the courses and STRING_AGG to build the course_key delimited list.

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;