使用SQL Server OPENJSON解析JSON时color字段返回NULL的问题

你的问题出在第二个OPENJSON的WITH子句使用方式不对：原JSON的colors是一个简单的字符串数组（每个元素就是纯文本值，不是带color键的对象），但你用WITH ([color] varchar(10))试图映射一个不存在的键，所以返回NULL。

下面是两种可行的修复方案：

方案一：直接引用默认的value列 #

去掉第二个OPENJSON的WITH子句，直接取数组元素的默认value字段作为color：

select
    a.CarBrand,
    a.CarModel,
    a.CarPrice,
    b.value as 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) as b

方案二：在WITH子句中指定路径$ #

如果一定要用WITH子句，需要明确指定路径为$（代表当前数组元素本身）：

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

执行任意一种方案后，都能得到你期望的结果：

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

内容的提问来源于stack exchange，提问作者Coldchain9