SQL查询优化：筛选JSON列含指定键且值为null的行

你当前的查询SELECT * FROM Table WHERE JSON_VALUE(Column, '$.test') IS NULL会把两类不符合预期的行也捞出来——没有test键的空JSON（{}）和不含test键的其他JSON（{"prod":1}），这是因为当指定键不存在时，JSON_VALUE会返回NULL，触发IS NULL的匹配条件。

要精准筛选存在指定键且对应值为NULL的行，根据你使用的SQL版本，可以用以下两种方案：

方案1：使用JSON_PATH_EXISTS（SQL Server 2022及以上版本） #

这个函数可以直接判断JSON路径是否存在，搭配JSON_VALUE的NULL判断就能实现需求：

SELECT * 
FROM [Table] 
WHERE JSON_PATH_EXISTS([Column], '$.test') = 1 
  AND JSON_VALUE([Column], '$.test') IS NULL

方案2：使用OPENJSON（兼容SQL Server 2017及更早版本） #

通过OPENJSON解析JSON列，直接筛选键名为test且值为NULL的行：

SELECT t.*
FROM [Table] t
CROSS APPLY OPENJSON(t.[Column]) j
WHERE j.[key] = 'test' 
  AND j.value IS NULL

这两个方案都只会返回{"test":null}这类符合预期的数据行，排除不含test键的记录。

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