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.