Azure SQL: get all values of JSON of a column

Question

I have simplified my problem for the sake of this question, I am trying to get all the ReferenceId and Options values of the JSON using SQL but I am getting NULL. Can someone help me with this?

DECLARE @jsonInfo VARCHAR(MAX)

SET @jsonInfo='{
"Resources": [
    {
        "Id": "fa611f1a-554b-4630-b0c6-4ec846452c8c",
        "Options": [
            "340e67c6-7b6f-4b15-82ad-d0198d44fc67",
            "3c743e42-c669-41f6-a807-4726fac39e0b"
        ]
    
    },
    {
        "Id": "360ed9dd-44bb-4758-abb8-dc914e13b443",
        "Options": [
            "3b4165d1-af46-486b-b9b0-8cc924d42be6"
        ]
    
    }
],
"References": [
    {
        "ReferenceId": "ea2f6534-d5f5-4028-b285-ad85b3c45f10"
    },
    {
        "ReferenceId": "f8fb63a0-f60b-4c77-a9f0-22ffdb3c07ac"
    },
    {
        "ReferenceId": "d8ce4f46-0417-4972-b098-83219b6c7a4f"
    
    }
]
}';

SELECT JSON_VALUE(@jsonInfo, '$.References.ReferenceId')
SELECT JSON_VALUE(@jsonInfo, '$.Resources.Options')

Answer 1

You can get a single value using this approach, something like this:

SELECT JSON_VALUE(@jsonInfo, '$.References[0].ReferenceId')

But if you want the whole list of values from the References (or Resources) array, you need to use OPENJSON and a defined "output shape" like this:

SELECT * 
FROM OPENJSON(@jsonInfo, N'$.References')
WITH (ReferenceId VARCHAR(50) '$.ReferenceId')

Now you should get:

ReferenceId
------------------------------------
ea2f6534-d5f5-4028-b285-ad85b3c45f10
f8fb63a0-f60b-4c77-a9f0-22ffdb3c07ac
d8ce4f46-0417-4972-b098-83219b6c7a4f