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

Answer 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