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

Question

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
)

Answer 1

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.