Parse nested JSON in SQL Server

Question

I have stored data in sql server as Json format, which as give below. I would like to retrieve it as normal string value. I have tried JSON_VALUE but json may or may not have more than one child, so it should retrieve all the values.

Input

TableA

   ID Education
   -------------------------------------------------------------------------------------
    1  {"Education": {"Record":[{"SLSubject":"MICRO ECONOMICS","Score":"77","Grade":"A"}]}}
    2  {"Education": {"Record":[{"SLSubject":"Math","Score":"89","Grade":"A"},{"SLSubject":"eng","Score":"88","Grade":"B"},{"SLSubject":"tam","Score":"33","Grade":"C"}]}}
    3  {"Education":{"Record":[{"SLSubject":"subject 1","Score":"87","Grade":"A"},{"SLSubject":"subject 2","Score":"67","Grade":"B"},{"SLSubject":"subject 3","Score":"45","Grade":"C"},{"SLSubject":"subject 4","Score":"87","Grade":"D"}]}}

Expected Output

ID Education
-------------------------------------------------------------------------------------
1  MICRO ECONOMICS - 77 - A
2  Math - 88 - B \n end - 88 - B \n Tam - 33 - C
3  subject 1 - 87- A \n subject 2 - 67- B \n subject 3 - 45- C \n subject 1 - 87- D \n

Query (which is working for one child)

SELECT ID, JSON_VALUE(Education,'$.Education.Record[0].SLSubject')
FROM TableA

Actual Result

ID Education
-------------------------------------------------------------------------------------
1  MICRO ECONOMICS - 77 - A
2  Math - 88 - B
3  subject 1 - 87- A

Sample JSON:

{"Education":{"Record":[{"SLSubject":"subject 1","Score":"87","Grade":"A"},{"SLSubject":"subject 2","Score":"67","Grade":"B"},{"SLSubject":"subject 3","Score":"45","Grade":"C"},{"SLSubject":"subject 4","Score":"87","Grade":"D"}]}}

We will send report to the user for validation. To send that report we need to show education record of the candidate. — Vignesh Kumar A
– Vignesh Kumar A, Commented Sep 17, 2019 at 15:43

Thom A · Accepted Answer · 2019-09-17 16:11:12Z

2

You need to use OPENJSON and treat your data as a dataset, JSON_VALUE is for returning a scalar value. This'll likely be what you really want:

SELECT YT.ID,
       OJ.SLSubject,
       OJ.Score,
       OJ.Grade
FROM (VALUES(1,N'[{"SLSubject":"MICRO ECONOMICS","Score":"77","Grade":"A"}]'),
            (2,N'[{"SLSubject":"Math","Score":"89","Grade":"A"},{"SLSubject":"eng","Score":"88","Grade":"B"},{"SLSubject":"tam","Score":"33","Grade":"C"}]'),
            (3,N'[{"SLSubject":"subject 1","Score":"87","Grade":"A"},{"SLSubject":"subject 2","Score":"67","Grade":"B"},{"SLSubject":"subject 3","Score":"45","Grade":"C"},{"SLSubject":"subject 4","Score":"87","Grade":"D"}]'))YT(ID,Education) 
     CROSS APPLY OPENJSON(YT.Education) 
                 WITH (SLSubject varchar(20),
                       Score int,
                       Grade char(1)) OJ;

Seems the OP wants this?

SELECT YT.ID,
       STRING_AGG(CONCAT(OJ.SLSubject, ' - ', OJ.Score, ' - ', OJ.Grade),' \n ') WITHIN GROUP (ORDER BY OJ.SLSubject) AS Education
FROM (VALUES(1,N'[{"SLSubject":"MICRO ECONOMICS","Score":"77","Grade":"A"}]'),
            (2,N'[{"SLSubject":"Math","Score":"89","Grade":"A"},{"SLSubject":"eng","Score":"88","Grade":"B"},{"SLSubject":"tam","Score":"33","Grade":"C"}]'),
            (3,N'[{"SLSubject":"subject 1","Score":"87","Grade":"A"},{"SLSubject":"subject 2","Score":"67","Grade":"B"},{"SLSubject":"subject 3","Score":"45","Grade":"C"},{"SLSubject":"subject 4","Score":"87","Grade":"D"}]'))YT(ID,Education) 
     CROSS APPLY OPENJSON(YT.Education) 
                 WITH (SLSubject varchar(20),
                       Score int,
                       Grade char(1)) OJ
GROUP BY YT.ID;

DB<>Fiddle

edited Sep 17, 2019 at 16:11

answered Sep 17, 2019 at 15:44

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

9 Comments

Vignesh Kumar A Over a year ago

Thanks but i wanted in single column as Subject, Score and Grade as combined.

Thom A Over a year ago

Subject, Score, and Grade are on a single row @VigneshKumarA . Do you mean "column"? A row is the horizontal part of the datagrid (in this case, will contain a single value for ID, Subject, Score, and Grade). A Column is the vertical part of the datagrid, so will contain all the values for a specific data piece (such as Score).

Thom A Over a year ago

Use CONCAT: CONCAT(OJ.SLSubject,'-',OJ.Score,'-',OJ.Grade)@VigneshKumarA

Vignesh Kumar A Over a year ago

But ID:2 will have more than one rows for education know

Thom A Over a year ago

So you want to aggregate the columns and rows? Like that @VigneshKumarA?

|

Collectives™ on Stack Overflow

Parse nested JSON in SQL Server

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related