sqljsonsql-serverrelational-divisionsql-server-json

How to query a table with JSON column with key-value pairs to match all keys and values


Say I have an Image table with a meta column in JSON:

Id Meta
1 { "size": 80, "effect": "blur" }
2 { "size": 200, "optimize": true }
3 { "color": "#abcdef", "ext": ".jpg" }

And I have a dynamic param of table type like so

Key Value
size 200
optimize true

How should I write my query to filter the rows in which the Meta column's key-value pairs matched all the values in the param table?

SELECT Id
FROM Image
WHERE (
  --?? all keys and values matched the param table
)

Solution

  • This is a type of relational division (with remainder) question, with the extra twist of shredding JSON at the same time.

    There are a number of solutions to this type of question. One common solution is to LEFT JOIN the divisor to the dividend, group it and check for any non-matches:

    DECLARE @tmp TABLE (
      "Key" NVARCHAR(8) COLLATE Latin1_General_BIN2,
      "Value" NVARCHAR(4) COLLATE Latin1_General_BIN2
    );
    
    INSERT INTO @tmp
      ("Key", "Value")
    VALUES
      ('size', '200'),
      ('optimize', 'true');
      
    SELECT *
    FROM Image i
    WHERE EXISTS (SELECT 1
        FROM @tmp t
        LEFT JOIN OPENJSON(i.Meta) j ON t.[Key] = j.[key] AND t.Value = j.value
        HAVING COUNT(j.value) = COUNT(*)  -- all match
    );
    

    Another solution is to use a double NOT EXISTS: there are no key/value input pairs which do not have a match

    DECLARE @tmp TABLE (
      "Key" NVARCHAR(8) COLLATE Latin1_General_BIN2,
      "Value" NVARCHAR(4) COLLATE Latin1_General_BIN2
    );
    
    INSERT INTO @tmp
      ("Key", "Value")
    VALUES
      ('size', '200'),
      ('optimize', 'true');
      
    SELECT *
    FROM Image i
    WHERE NOT EXISTS (SELECT 1
        FROM @tmp t
        WHERE NOT EXISTS (SELECT 1
            FROM OPENJSON(i.Meta) j
            WHERE t.[Key] = j.[key] AND t.Value = j.value
        )
    );
    

    db<>fiddle

    YMMV as to which solution is faster.