select query for json in sql

Question

I have below json in sql:

declare @jObject as nvarchar(max)='{
    "StudentData": [
        {
            "StudentID":5000,
            "StudentName":"xyz"

        },
        {
            "StudentID":5001,
            "StudentName":"abc"

        }
        ]

}'

I want to write select query such that :-

StudentID  StudentName
5000        xyz
5001        abc

I tried below query :-

SELECT *  
FROM OPENJSON(@jObject) 
WITH (StudentID int '$.StudentID',StudentName varchar(50) '$.StudentName')

But this is returning me single row and null values for both columns. Please help.

Also tried below which is not working :-

EDIT 1 :-

SELECT *  
FROM OPENJSON(@jObject) 
WITH (StudentID int '$.StudentData.StudentID',StudentName varchar(50) '$.StudentData.StudentName')

You have an object with a StudentData property which contains objects with StudentID, ` StudentName` properties. Try '$.StudentData.StudentID' — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Feb 2, 2018 at 12:54

Panagiotis Kanavos · Accepted Answer · 2018-02-02 13:00:10Z

3

Your JSON string contains an object with a StudentData property which contains an array of objects with StudentID, StudentName properties. You need to query the contents of the StudentData property, not the root object.

To do that, pass the property's path to OPENSJON :

SELECT *  
FROM OPENJSON(@jObject,'$.StudentData') 
WITH (
    StudentID int '$.StudentID',
    StudentName varchar(50) '$.StudentName')

The result is :

StudentID   StudentName
5000        xyz
5001        abc

answered Feb 2, 2018 at 13:00

Panagiotis Kanavos

134k16 gold badges211 silver badges271 bronze badges

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

1 Comment

C Sharper Over a year ago

Thanks for sharing answer and your valuable time :)

C Sharper · Accepted Answer · 2018-02-02 13:04:30Z

0

Note :- Answer suggested by @Panagiotis Kanavos worked 100%

I tried below query by myself and it also worked :-

SELECT    StudentID ,StudentName 
FROM OPENJSON(@jObject) 
WITH (StudentData nvarchar(max) AS JSON)
CROSS APPLY 
            OPENJSON (StudentData)
            WITH ( StudentName nvarchar(max), StudentID int)

answered Feb 2, 2018 at 13:04

C Sharper

8,66430 gold badges96 silver badges158 bronze badges

1 Comment

Panagiotis Kanavos Over a year ago

You are parsing the object twice in this case

Collectives™ on Stack Overflow

select query for json in sql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related