Parsing json with SQL Server OpenJson

Question

I am trying to parse the following json:

declare @json nvarchar(2048) = N'{
"brand": "BMW",
"year": 2019,
"price": 1234.6,
"colors": ["red","blue","black"]
}'

using:

select
    a.CarBrand,
    a.CarModel,
    a.CarPrice,
    b.color
from
openjson(@json)
with  
(
    CarBrand varchar(100) '$.brand',
    CarModel int '$.year',
    CarPrice money '$.price',
    colors nvarchar(max) as json
) as a
cross apply openjson(a.colors)
with
(
    [color] varchar(10)
) as b

but I keep receiving nulls for the color. I'm still trying to understand the openjson and cross apply syntax but I can't quite get this to work.

Current outcome:

CarBrand    CarModel    CarPrice    color
BMW         2019        1234.60     NULL
BMW         2019        1234.60     NULL
BMW         2019        1234.60     NULL

Desired outcome:

CarBrand    CarModel    CarPrice    color
BMW         2019        1234.60     red
BMW         2019        1234.60     blue
BMW         2019        1234.60     black

so if I have a single (non-nested array) I just need to tell it to extract each value via '$'? How simple! — Coldchain9, Oct 03 '22 at 13:47

score 1 · Answer 1 · answered Oct 03 '22 at 13:46

1

colors is an array; it doesn't have a key called color. Just use OPENJSON with no WITH clause and return the value for your colors array:

DECLARE @json nvarchar(2048) = N'{
    "brand": "BMW",
    "year": 2019,
    "price": 1234.6,
    "colors": ["red","blue","black"]
}';


SELECT OJ.CarBrand,
       OJ.CarModel,
       OJ.CarPrice,
       C.value AS color
FROM OPENJSON(@json)
         WITH (CarBrand varchar(100) '$.brand',
               CarModel int '$.year',
               CarPrice money '$.price',
               colors nvarchar(MAX) AS JSON) OJ
     CROSS APPLY OPENJSON(OJ.colors) C;

answered Oct 03 '22 at 13:46

Thom A

88,727
11
45
75

Lets say ```"price"``` was also an array of 3 unique items. How could I handle exploding the price array alongside the colors array and maintain the order? – Coldchain9 Oct 03 '22 at 14:11
Sounds like you have a [new question](https://stackoverflow.com/questions/ask) @Coldchain9 . – Thom A Oct 03 '22 at 14:20

Zhorov · Accepted Answer · 2022-10-03T14:19:45.653

If you want to use an explicit schema (the WITH clause), you need to define the path (... [color] varchar(10) '$' ... ) in the second OPENSJON() call:

select a.CarBrand, a.CarModel, a.CarPrice, b.color
from openjson(@json) with (
    CarBrand varchar(100) '$.brand',
    CarModel int '$.year',
    CarPrice money '$.price',
    colors nvarchar(max) as json
) as a
cross apply openjson(a.colors) with (
    [color] varchar(10) '$'
) as b

Parsing json with SQL Server OpenJson

2 Answers2

Related