Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

SQL Server JSON Queries

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:

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:

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?

Tags:

2

Answers


  1. 0

    Looks like you want aggregation over the whole OPENJSON set. You can use conditional aggregation in a HAVING for this.

    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.

    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'
);
    Login or Signup to reply.
  2. 0

    The following statement provides an option to query the stored JSON for multiple keys including possible NULL values.

    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
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search