SQL Server : SELECT JSON Column in this JSON Structure

Question

I would like to know will it possible to select data from below JSON structure?

 [
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 3, 
        "Status": 0
    },
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 7, 
        "Status": 0
    }
]

According this link, there is Property before the array/object.

"EmployeeInfo": {  
        "FirstName":"Jignesh",  
        "LastName":"Trivedi",  
        "Code":"CCEEDD",  
        "Addresses": [  
            { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
            { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
        ]  
    } 

For example, (getting sample from above link), we see the query is started with property EmployeeInfo so that make query possible to get data in this query.

SELECT JSON_VALUE(@JSONData, '$.EmployeeInfo.FirstName')

So I just can't figure out how could this be achieve from the structure provide above, anyone could point me to some sample code that will be helpful. Thanks.

Answer 1

You have two options to parse this JSON array:

• Using OPENJSON() with explicit schema once - to get the content of each item
• Using OPENJSON() twice - to get the index and the content of each item

JSON:

DECLARE @json varchar(max) = '
[
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 3, 
        "Status": 0
    },
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 7, 
        "Status": 0
    }
]'

Using OPENJSON() with explicit schema once:

SELECT A, Status
FROM OPENJSON(@json) WITH (
   A int,
   Status int
)

Result:

A   Status
6   1
3   0
6   1
7   0

Using OPENJSON() twice:

SELECT 
   j1.[key] AS Index,
   j2.A, j2.Status
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.[value]) WITH (
   A int,
   Status int
) j2 

Result:

Index   A   Status
0       6   1
1       3   0
2       6   1
3       7   0

Of course, you can always access an array item by index:

SELECT 
   JSON_QUERY(@json, '$[0]') AS Item,
   JSON_VALUE(@json, '$[0].A') AS A, 
   JSON_VALUE(@json, '$[0].Status') AS Status

Result:

Item                   A    Status
{"A": 6, "Status": 1}  6    1

Answer 2

Something like this

declare @json nvarchar(max) =N'
[
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 3, 
        "Status": 0
    },
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 7, 
        "Status": 0
    }
]'

select * from openjson(@json) with (A       int,
                                    Status  int);

Output

A   Status
6   1
3   0
6   1
7   0