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
– Coldchain9, Commented Oct 3, 2022 at 13:47

Zhorov · Accepted Answer · 2022-10-03 14:19:45Z

1

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

edited Oct 3, 2022 at 14:19

answered Oct 3, 2022 at 13:47

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thom A · Accepted Answer · 2022-10-03 13:46:43Z

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 3, 2022 at 13:46

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

2 Comments

Coldchain9 Over a year ago

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?

Thom A Over a year ago

Sounds like you have a new question @Coldchain9 .

Collectives™ on Stack Overflow

Parsing json with SQL Server OpenJson

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related