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

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:

Thanks

CodePudding user response：

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