SQL Server : SELECT JSON Column in this JSON Structure

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.

Solution

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