I have a nvarchar(max) column that contains a string array of tags. I am querying this columns for matching values using the JSON functions. While I do have it working, I wasn't sure if there was a better way using other built-in functions.
IF(NOT EXISTS(SELECT 1 FROM #temp))
BEGIN
CREATE TABLE #temp (Id int IDENTITY, Tags nvarchar(max));
INSERT INTO #temp (Tags) VALUES ('["red", "blue", "green"]');
INSERT INTO #temp (Tags) VALUES ('["red", "yellow", "blue"]');
INSERT INTO #temp (Tags) VALUES ('["red", "purple", "orange"]');
END
SELECT * FROM #temp CROSS APPLY OPENJSON(Tags) WITH (T nvarchar(100) '$') tags WHERE tags.T = 'red';
SELECT * FROM #temp CROSS APPLY OPENJSON(Tags) WITH (T nvarchar(100) '$') tags WHERE tags.T = 'blue';
I've seen quite a few ways to query JSON data and wasn't sure if this was the best practice for querying this data. JSON_QUERY uses a $.property format that I don't have because this is a simple single-dimension array.
Is there a better way?
We can use the fact that OPENJSON returns 3 columns: key, value and type.
Example query: SELECT * FROM OPENJSON('["v1", "v2", "v3"]')
Output:
key value type
--- --- ---
0 v1 1
1 v2 1
2 v3 1
We can use this to construct the query:
-- DROP TABLE IF EXISTS table1
CREATE TABLE table1 (
ID INT NOT NULL PRIMARY KEY,
Tags NVARCHAR(100) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO table1
VALUES
(1, '["v1", "v2", "v3"]'),
(2, '["q1", "q2", "q3"]'),
(3, '["z1", "z2", "z3"]')
GO
-- To find rows where a single value is present in the JSON array:
SELECT * FROM table1
WHERE 'v2' IN (
SELECT value -- Here we use `value` that is returned by OPENJSON()
FROM OPENJSON(table1.Tags)
)
-- To find rows containing at least 1 of multiple values (first OR second OR ...):
SELECT * FROM table1
WHERE EXISTS (
SELECT 1
FROM OPENJSON(table1.Tags)
WHERE value IN ('v1', 'v2', 'z3') -- Here we use `value` returned by OPENJSON()
)
Output of first query is data row 1 because its JSON contains a value that is equal to 'v2':
ID Tags
--- ---
1 ["v1", "v2", "v3"]
Output of second query is data rows 1 and 3 because they match 'v1' or 'v2' or 'z3':
ID Tags
--- ---
1 ["v1", "v2", "v3"]
3 ["z1", "z2", "z3"]