Using OPENJSON in SQL Server Query

Question

I'm trying to pull some information from a JSON array stored in an SQL Server database. I can see I need to use OPENJSON, and I've found numerous examples of how to use it, but the rub is all of the examples demonstrate declaring a single JSON array as a variable and using OPENJSON to open it (e.g. How to read field name with space in Json using OPENJSON in SQL Server 2016). I'd like to know how to actually build this into a database query.

Example structure of database table:

Table: HELP_INFO

UID	ID	JSON
1	45745	JSON varchar - example below
2	45745	JSON varchar - example below
3	82567	JSON varchar - example below

Example structure of the JSON

Example 1
    {"source":"XYZ",
     "source_info":{
         "type":"need_help",
         "help_info":{     
                "questions":50, 
                "details":[{"floatv":0.42,"name":"Tom"},{"floatv":1.08,"name":"Dick"},{"floatv":1.2,"name":"Harry"}]}}}

Example 2
    {"source":"XYZ",
     "source_info":{
         "type":"need_help",
         "help_info":{     
                "questions":12, 
                "details":[{"floatv":2.0,"name":"Donald"},{"floatv":0.4,"name":"Mickey"}]}}}

For each row in the table I need to open the "details" list in the JSON and extract all of the "name" values. The items in the details list always have the same keys, but the number of items in the list varies.

How would I write a query to do this, getting the source JSON values and their ID from rows on the HELP_INFO table?

Desired output:

UID	NAMES
45745	Tom Dick Harry
45745	Donald Mickey
82567	Other names

Thanks

Answer 1

You need an OPENJSON() call with the appropriate path to access the nested $.source_info.help_info.details JSON array and STRING_AGG() to aggregate the names:

Sample data:

SELECT *
INTO Data
FROM (VALUES
   (1, 45745, '{"source":"XYZ",
     "source_info":{
         "type":"need_help",
         "help_info":{     
                "questions":50, 
                "details":[{"floatv":0.42,"name":"Tom"},{"floatv":1.08,"name":"Dick"},{"floatv":1.2,"name":"Harry"}]}}}')
) v (UID, ID, JSON)

Statement:

SELECT 
   ID, 
   NAMES = (
      SELECT STRING_AGG(JSON_VALUE([value], '$.name'), ' ') 
             WITHIN GROUP (ORDER BY CONVERT(int, [key]))
      FROM OPENJSON(JSON, '$.source_info.help_info.details')
   )
FROM Data