Select and display named elements of an array in a JSON column

Question

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?

Return the data as a set, not as scalar values, and then string aggregate. — Thom A
– Thom A ♦, Commented Dec 24, 2022 at 10:35
Will have to look up how to do that. Guessing that's json_query. — Velocedge
– Velocedge, Commented Dec 24, 2022 at 12:55

Dan Guzman · Accepted Answer · 2022-12-24 12:46:06Z

1

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;

person_id	event_date	course_key
1	2022-12-21	LEND1254,LEND1211

answered Dec 24, 2022 at 12:46

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Velocedge Over a year ago

Awesome! Put it into a subquery and it's exactly what I needed

Collectives™ on Stack Overflow

Select and display named elements of an array in a JSON column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related