SQL query to check if a key exists and its value is null in JSON column
Extending this question, if I want to query for multiple keys, it works fine for me with both AND and OR in a SQL Server 2022 version:
<code>SELECT *
FROM Logs
WHERE
JSON_PATH_EXISTS(Parameters, '$.Name') = 1 AND
JSON_PATH_EXISTS(Parameters, '$.Guid') = 1 AND
(JSON_VALUE([Parameters], '$.Name') = 'Test') AND /* here testing both AND and OR */
(JSON_VALUE([Parameters], '$.Guid') = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a');
</code>
<code>SELECT *
FROM Logs
WHERE
JSON_PATH_EXISTS(Parameters, '$.Name') = 1 AND
JSON_PATH_EXISTS(Parameters, '$.Guid') = 1 AND
(JSON_VALUE([Parameters], '$.Name') = 'Test') AND /* here testing both AND and OR */
(JSON_VALUE([Parameters], '$.Guid') = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a');
</code>
SELECT *
FROM Logs
WHERE
JSON_PATH_EXISTS(Parameters, '$.Name') = 1 AND
JSON_PATH_EXISTS(Parameters, '$.Guid') = 1 AND
(JSON_VALUE([Parameters], '$.Name') = 'Test') AND /* here testing both AND and OR */
(JSON_VALUE([Parameters], '$.Guid') = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a');
However, if I try to adjust SQL Server 2016+ example, it does wrong with AND:
<code>SELECT *
FROM [Logs] t
WHERE EXISTS (
SELECT 1
FROM OPENJSON(t.[Parameters])
WHERE ([key] = 'Name' AND [value] = 'Test') AND /* _here is problem_ */
([key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a')
)
</code>
<code>SELECT *
FROM [Logs] t
WHERE EXISTS (
SELECT 1
FROM OPENJSON(t.[Parameters])
WHERE ([key] = 'Name' AND [value] = 'Test') AND /* _here is problem_ */
([key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a')
)
</code>
SELECT *
FROM [Logs] t
WHERE EXISTS (
SELECT 1
FROM OPENJSON(t.[Parameters])
WHERE ([key] = 'Name' AND [value] = 'Test') AND /* _here is problem_ */
([key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a')
)
If I place OR in a problematic place, it queries fine – if both pairs do not match, nothing returns, if one or both statements match – row is returned correctly.
However, if I do AND query, if both pairs match, nothing is returned still. How do I fix second query so it works?
7
Looks like you want aggregation over the whole OPENJSON set. You can use conditional aggregation in a HAVING for this.
<code>SELECT
l.*
FROM Logs l
WHERE EXISTS (SELECT 1
FROM OPENJSON(l.Parameters) j
HAVING COUNT(CASE WHEN j.[key] = 'Name' AND j.value = 'Test' THEN 1 END) > 0
AND COUNT(CASE WHEN j.[key] = 'Guid' AND j.value = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a' THEN 1 END) > 0
);
</code>
<code>SELECT
l.*
FROM Logs l
WHERE EXISTS (SELECT 1
FROM OPENJSON(l.Parameters) j
HAVING COUNT(CASE WHEN j.[key] = 'Name' AND j.value = 'Test' THEN 1 END) > 0
AND COUNT(CASE WHEN j.[key] = 'Guid' AND j.value = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a' THEN 1 END) > 0
);
</code>
SELECT
l.*
FROM Logs l
WHERE EXISTS (SELECT 1
FROM OPENJSON(l.Parameters) j
HAVING COUNT(CASE WHEN j.[key] = 'Name' AND j.value = 'Test' THEN 1 END) > 0
AND COUNT(CASE WHEN j.[key] = 'Guid' AND j.value = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a' THEN 1 END) > 0
);
A better idea might be to use OPENJSON with a schema, then you only get one row for the whole object, with actual properties as columns broken out.
<code>SELECT
l.*
FROM Logs l
WHERE EXISTS (SELECT 1
FROM OPENJSON(l.Parameters)
WITH (
Name nvarchar(100),
Guid uniqueidentifier
) j
WHERE j.Name = 'Test'
AND j.Guid = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a'
);
</code>
<code>SELECT
l.*
FROM Logs l
WHERE EXISTS (SELECT 1
FROM OPENJSON(l.Parameters)
WITH (
Name nvarchar(100),
Guid uniqueidentifier
) j
WHERE j.Name = 'Test'
AND j.Guid = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a'
);
</code>
SELECT
l.*
FROM Logs l
WHERE EXISTS (SELECT 1
FROM OPENJSON(l.Parameters)
WITH (
Name nvarchar(100),
Guid uniqueidentifier
) j
WHERE j.Name = 'Test'
AND j.Guid = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a'
);
The following statement provides an option to query the stored JSON for multiple keys including possible NULL values.
<code>SELECT *
FROM [Logs]
WHERE EXISTS (
SELECT 1
FROM OPENJSON(Parameters)
WHERE
([key] = 'Name' AND [value] = 'Test') OR
([key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a')
-- or possible NULL values
--([key] = 'Guid' AND [value] IS NULL)
HAVING COUNT(DISTINCT [key]) = 2
)
</code>
<code>SELECT *
FROM [Logs]
WHERE EXISTS (
SELECT 1
FROM OPENJSON(Parameters)
WHERE
([key] = 'Name' AND [value] = 'Test') OR
([key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a')
-- or possible NULL values
--([key] = 'Guid' AND [value] IS NULL)
HAVING COUNT(DISTINCT [key]) = 2
)
</code>
SELECT *
FROM [Logs]
WHERE EXISTS (
SELECT 1
FROM OPENJSON(Parameters)
WHERE
([key] = 'Name' AND [value] = 'Test') OR
([key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a')
-- or possible NULL values
--([key] = 'Guid' AND [value] IS NULL)
HAVING COUNT(DISTINCT [key]) = 2
)