Parsing json with SQL Server OpenJson-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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;