Search code examples
sqljsonsql-servert-sqlopen-json

Getting NULL with OPENJSON read JSON with SQL


I have JSON like below. and I want to find all models from the JSON.

When I give below SQL query it gives me only Sedan models

DECLARE @PermsJSON NVARCHAR(MAX) =<json>;

select * from OPENJSON(@PermsJSON, '$.Models[0].Make')

But, when I gave below its giving null (removing [0]).

DECLARE @PermsJSON NVARCHAR(MAX) =<json>;

select * from OPENJSON(@PermsJSON, '$.Models.Make')

What should I do if I want to get list of Models irrespective of Make and Models?

Something like: "Corolla", "Camry", "Civic", "Santa", "Tucson", "Bronco"

{
    "Root": "Vehicles",
    "Models": [
        {
            "Type": "Sedan",
            "Make": [
                {
                    "color": "Red",
                    "name": "Toyota",
                    "Models": [
                        "Corolla",
                        "Camry"
                    ]
                },
                {
                    "color": "Blue",
                    "Make": "Honda",
                    "Models": [
                        "Civic"
                    ]
                }
            ]
        },
        {
            "Type": "SUV",
            "Make": [
                {
                    "color": "White",
                    "name": "Hyundai",
                    "Models": [
                        "Santa",
                        "Tucson"
                    ]
                },
                {
                    "color": "Black",
                    "Make": "Ford",
                    "Models": [
                        "Bronco"
                    ]
                }
            ]
        }
    ]
}

Solution

  • Since your values are part of individual nested arrays, perhaps this will give you a nudge

    Example

    Select C.Value 
     From OpenJSON(@JSON ,'$.Models' ) A
     Cross Apply  OpenJSON(A.value,'$.Make' ) B
     Cross Apply  OpenJSON(B.value,'$.Models' ) C
    

    Results

    Value
    Corolla
    Camry
    Civic
    Santa
    Tucson
    Bronco