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
)
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
)
);
YMMV as to which solution is faster.