Query json array with no property names

Question

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?

Answer 1

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"]