英文:
SQL query to check if a key exists and its value is null in JSON column
问题
I am trying to select rows that have a JSON column where there is an object with a given key and the value is null.
Example:
{
"test": null
}
I tried the below query:
SELECT * FROM Table WHERE JSON_VALUE(Column, '$.test') IS NULL
Result columns:
- {"test":null}
- {}
- {"prod":1}
This returns the expected rows but also returns rows that don't have such a key.
How to return only the rows that have the key exist.
英文:
I am trying to select rows that has a JSON column where there is an object with a given key and value is null.
Example:
{
"test": null
}
I tried the below query:
SELECT * FROM Table WHERE JSON_VALUE(Column, '$.test') IS NULL
Result columns:
- {"test":null}
- {}
- {"prod":1}
This returns the expected rows but also returns rows that doesn't have such key.
How to return only the rows that have the key exist.
答案1
得分: 1
SQL Server 2016+:
您可以尝试使用OPENJSON()和默认模式解析JSON。结果 是一个包含列key（一个nvarchar(4000)值，包含指定属性的名称）、value（一个nvarchar(max)值，包含属性的值）和type的表，因此您可以实现适当的WHERE子句。
SELECT *
FROM [Table] t
WHERE EXISTS (
SELECT 1
FROM OPENJSON(t.[Column])
WHERE [key] = N'test' AND [value] IS NULL
)
SQL Server 2022+:
另一个选择是结合使用JSON_PATH_EXISTS()和JSON_VALUE()：
SELECT *
FROM [Table]
WHERE
JSON_PATH_EXISTS([Column], '$.test') = 1 AND
JSON_VALUE([Column], '$.test') IS NULL
注意:
当您使用JSON_VALUE()和strict路径修饰符时，对于错误的路径表达式，结果将是错误的：
SELECT *
FROM [Table]
WHERE JSON_VALUE([Column], 'strict $.test') IS NULL
英文:
SQL Server 2016+:
You may try to parse the JSON with OPENJSON() and default schema. The result is a table with columns key (an nvarchar(4000) value that contains the name of the specified property), value (an nvarchar(max) value that contains the value of the property) and type, so you can implement an appropriate WHERE clause.
SELECT *
FROM [Table] t
WHERE EXISTS (
SELECT 1
FROM OPENJSON(t.[Column])
WHERE [key] = N'test' AND [value] IS NULL
)
SQL Server 2022+:
Another option is a combination of JSON_PATH_EXISTS() and JSON_VALUE():
SELECT *
FROM [Table]
WHERE
JSON_PATH_EXISTS([Column], '$.test') = 1 AND
JSON_VALUE([Column], '$.test') IS NULL
Note:
When you use JSON_VALUE() and a strict path modifier, the result is an error for a wrong path expression:
SELECT *
FROM [Table]
WHERE JSON_VALUE([Column], 'strict $.test') IS NULL
答案2
得分: -2
You have to use JSON_EXTRACT
For example:
Select *
from table
where JSON_EXTRACT(Column, '$.test') IS NOT NULL
英文:
You have to use JSON_EXTRACT
For example:
Select *
from table
where JSON_EXTRACT(Column, '$.test') IS NOT NULL
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论