Is it possible to use wildcards as an argument for OPENJSON in SQL Server?

Question

I have a nested JSON array consisting of outer keys that are numbers, each of which contain inner arrays that I need to import into a table in SQL Server. The JSON file is setup like so:

{
    "121212": {
        "name": name of item,
        "subject": item subject
    },
    "343434": {
        "name": name of item,
        "subject": item subject
    }
}

I can use the SQL Server function OPENJSON() to import a single array without issue like so:

DECLARE @arrayVariable VARCHAR(MAX)
SELECT @arrayVariable = BulkColumn FROM OPENROWSET(BULK 'array.json', SINGLE_BLOB) JSON
INSERT INTO ArrayTable (arrayName, arraySubject)
SELECT * FROM OPENJSON(@arrayVariable, '$."121212"') 
WITH (
     arrayName    VARCHAR(MAX)    '$.name',
     arraySubject VARCHAR(MAX)    '$.subject'
)

The above code successfully imports array 121212 into the ArrayTable. However, I would like to know if there is a solution that can utilize wildcards as an argument for OPENJSON in order to import in all numeric array keys from the JSON array, that way they don't have to be imported individually. I have tried using wildcards but none of the formatting options I've tried have worked so far. For example:

OPENJSON(@arrayVariable, '$."[0-9]%"')

What would be the best way to import all of the numerically titled JSON arrays using OPENJSON()?

Answer 1

Try this

DECLARE @arrayVariable VARCHAR(MAX) = N'{
    "121212": {
        "name": "name of item1",
        "subject": "item subject1"
    },
    "343434": {
        "name": "name of item2",
        "subject": "item subject2"
    }
}'

SELECT      v.arrayName, v.arraySubject
FROM        OPENJSON(@arrayVariable) AS r
CROSS APPLY OPENJSON(r.value) 
            WITH (
                 arrayName    VARCHAR(MAX)    '$.name',
                 arraySubject VARCHAR(MAX)    '$.subject'
            ) AS v
WHERE       r.[key] LIKE '[0-9]%'