Using OPENJSON in SQL Server to parse a Non-Array Object

Question

I'm using SQL Server v15, called from a .NET application.

A website I'm using (not mine - I don't control the data) has a JSON dataset formatted strangely. Instead of being an array like:

[{"id":"1","Name":"Charlie"},{"id":"2","Name"="Sally"}]

It's an object with each element named as its ID:

{"1":{"id":"1","Name":"Charlie"}, "2":{"id":"2","Name"="Sally"}}

I know how to use the OPENJSON to read data from an array, but is it possible to have it parse this format? Or is my best bet to have a script loop through the objects one at a time?

Answer 1

Please try the following solution.

SQL

DECLARE @json NVARCHAR(MAX) = 
N'{
    "1": {
        "id": "1",
        "Name": "Charlie"
    },
    "2": {
        "id": "2",
        "Name": "Sally"
    }
}';

SELECT rs.*
FROM OPENJSON (@json) AS seq
CROSS APPLY OPENJSON(seq.value)
WITH 
(
   [id]     INT         '$.id'
   , [Name] VARCHAR(20) '$.Name'
) AS rs;

Output

id	Name
1	Charlie
2	Sally