Parse JSON structure in SQL

Question

I have written a code that reads information from JSON lines to table in SQL:

declare @pJSON varchar(max) = '
{
    "School": "MiddleSchool",
    "Password": "SchoolPassword",
    "Attributes": [
        {
            "Type": "Exam",
            "Value": "1"
        },
        {
            "Type": "Class",
            "Value": "11b"
        },
        {
            "Type": "Math",
            "Value": [
               {
                    "ExamDate": "2019-01-01",
                    "Points": 100,
                    "Grade": 10,
                    "Notes": "Good"
                }
            ]
        }   
    ]   
}   '


select ExamDate, Points, Grade, Notes   
from OPENJSON(@pJSON, N'$.Attributes[2].Value')    
cross apply openjson ([Value])   
with    
(    
  ExamDate date,    
  Points int,    
  Grade int,    
  Notes varchar(max)    
) as [value]

Code works fine, but I really hate N'$.Attributes[2].Value' part. Exam information can be in the first, second, third place, so [2] doesn't really work for me. Do you have any suggestions for me, how I can improve this code? Thank you!

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

You could use JSON_QUERY:

select ExamDate, Points, Grade, Notes   
from OPENJSON(JSON_QUERY(@pJSON, N'$.Attributes'))
with    
(    
  ExamDate date      N'$.Value[0].ExamDate',  -- here 0 because Value is array too
  Points int         N'$.Value[0].Points',    
  Grade int          N'$.Value[0].Grade',    
  Notes varchar(max) N'$.Value[0].Notes'
) as [value]
WHERE ExamDate IS NOT NULL;

db<>fiddle demo

EDIT:

In original question there was only one exam in array. If array could contain more than code should be adjusted:

SELECT s2.[key]
      ,ExamValue = JSON_VALUE(s2.value, '$.ExamDate')
      ,Points    = JSON_VALUE(s2.value, '$.Points')
      ,Grade     = JSON_VALUE(s2.value, '$.Grade')
      ,Notes     = JSON_VALUE(s2.value, '$.Notes')
FROM OPENJSON(JSON_QUERY(@pJSON, N'$.Attributes')) s
CROSS APPLY OPENJSON(JSON_QUERY(s.value, N'$.Value')) s2;

-- or
SELECT [value].*
FROM OPENJSON(JSON_QUERY(@pJSON, N'$.Attributes'))
CROSS APPLY OPENJSON(JSON_QUERY(value, N'$.Value'))
with    
(    
  ExamDate date      N'$.ExamDate', 
  Points int         N'$.Points',    
  Grade int          N'$.Grade',    
  Notes varchar(max) N'$.Notes'
) as [value];

db<>fiddle demo

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 26, 2019 at 17:05

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

neringab Over a year ago

Hello, thank you for an answer. However, it doesn't work properly when I have two exam arrays. Your.code takes only first. Do you know how to fix that?

Lukasz Szozda Over a year ago

@Lesley.H Hello, first of all SO is not a code service. For next time please post a sample that show your real example using dbfiddle.uk simplified scenarios are good for starting point only ;)

Collectives™ on Stack Overflow

Parse JSON structure in SQL

1 Answer 1

EDIT:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related