0

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?

Tony Basallo
  • 3,000
  • 2
  • 29
  • 47
  • 2
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Jul 10 '23 at 18:18
  • Why do you need distinct if you select a single tag from the array? – siggemannen Jul 10 '23 at 18:27
  • @siggemannen I don't (view on the actual query was messing with me). I updated the text. Thanks – Tony Basallo Jul 10 '23 at 18:47
  • @YitzhakKhabinsky I added that and re-wrote the ask a bit clearer (I think) – Tony Basallo Jul 10 '23 at 19:00
  • Please provide ##1-4 in its entirety. – Yitzhak Khabinsky Jul 10 '23 at 19:08

1 Answers1

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"]
Peter B
  • 22,460
  • 5
  • 32
  • 69