Get key names from the JSON

Question

How can I get list of key names from following JSON? (A1, A2)

DECLARE @json NVARCHAR(MAX);
SET @json = '{
    "Model": [{
        "A1": {
            "read": [
                "jcc",
                "rdbro"
            ],
            "write": [
                "jcc"
            ]
        },
        "A2": {
            "read": [
                "jcc",
                "rdbro",
                "rdbrw"
            ],
            "write": [
                "foo"
            ]
        }
    }]
}'

SELECT *
 FROM OPENJSON(@json, '$.Model') AS oj

Answer 1

Next statement should give you the key names. Model is a JSON array, so you need an additional APPLY operator and OPENJSON() call with default schema, which returns table with columns key, value and type:

Json data:

DECLARE @json NVARCHAR(MAX);
SET @json = '{
    "Model": [{
        "A1": {
            "read": [
                "jcc",
                "rdbro"
            ],
            "write": [
                "jcc"
            ]
        },
        "A2": {
            "read": [
                "jcc",
                "rdbro",
                "rdbrw"
            ],
            "write": [
                "foo"
            ]
        }
    }]
}'

Statement:

SELECT j2.[key] 
FROM OPENJSON(@json, '$.Model') j1
CROSS APPLY OPENJSON(j1.[value]) j2

Output:

----
key
----
A1
A2