Skip to main content

Return to Answer

Additional edits
Source Link
Zhorov
Zhorov
  • 29.5k
  • 6
  • 29
  • 54

You need an OPENJSON() call with the appropriate path to access the nested $.source_info.help_info.details JSON array and an additional APPLYSTRING_AGG() operatorto 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 d.
   ID, j.NAME
FROM Data d NAMES = (
CROSS APPLY OPENJSON    SELECT STRING_AGG(d.JSONJSON_VALUE([value], '$.source_info.help_info.details'name'), WITH' (') 
   NAME varchar         WITHIN GROUP (100ORDER BY CONVERT(int, [key]))
      FROM OPENJSON(JSON, '$.name'source_info.help_info.details')
   )
FROM jData

Result:

IDNAME
45745Tom
45745Dick
45745Harry

You need an OPENJSON() call with the appropriate path to access the nested $.source_info.help_info.details JSON array and an additional APPLY operator:

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 d.ID, j.NAME
FROM Data d
CROSS APPLY OPENJSON(d.JSON, '$.source_info.help_info.details') WITH (
   NAME varchar(100) '$.name'
) j

Result:

IDNAME
45745Tom
45745Dick
45745Harry

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
Source Link
Zhorov
Zhorov
  • 29.5k
  • 6
  • 29
  • 54

You need an OPENJSON() call with the appropriate path to access the nested $.source_info.help_info.details JSON array and an additional APPLY operator:

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 d.ID, j.NAME
FROM Data d
CROSS APPLY OPENJSON(d.JSON, '$.source_info.help_info.details') WITH (
   NAME varchar(100) '$.name'
) j

Result:

ID NAME
45745 Tom
45745 Dick
45745 Harry