OpenJson using a wildcard

Question

I have a SQL query using OPENJSON to import JSON data into a table. My problem is that the data I need is nested. How can I use a wildcard in the JSON path to get what I need?

SELECT @Set = 
BulkColumn FROM OPENROWSET
(BULK 'Sets.json', DATA_SOURCE = 'MyAzureJson', SINGLE_BLOB) JSON;

INSERT INTO [Sets]
SELECT [name]
FROM OPENJSON(@Set)
WITH(
    [name]      nvarchar(50)    '$.*.name'
)

my json file is set up like this..

{
    "testOne" : {
        name: "nameOne"
    },
    "testTwo : {
        name: "nameTwo"
    }
}

the error I'm getting with everything I try..

JSON path is not properly formatted. Unexpected character '*' is found at position 2.

I've tried . * [] and nothing works

Nurp · Answer 1 · 2018-10-29T12:55:34.667

As far as I know there is no support for wildcards in OPENJSON. Instead you can do a workaround by ignoring the field name in your search. Use JSON_VALUE for this.

INSERT INTO [Sets]
SELECT
    JSON_VALUE([value], '$.name')
FROM 
    OPENJSON(@Set)

Explanation: If you don't define the variables of OPENJSON inside a WITH clause and instead do a simple SELECT * FROM OPENJSON(@Set) query, you will get a result with key, value and type columns (see example output below). Because key contains your problematic field name, you can ignore that part and just look into the value column of the data.

[key]      [value]                [type]
-----      -------                ------
testOne    { name: "nameOne" }    5
testTwo    { name: "nameTwo" }    5

OpenJson using a wildcard

1 Answers1