How to query a JSON column (or a varchar column containing JSON) when the value is an array

I have a column inside an Azure SQL database to store JSON data.

The JSON data is an array in the following format:

[{"code": "A1", name": "Mark Smith"}, {"code": "B45", "name": "John Doe"}]

[{"code": "E7", name": "Ruby Lane"}]

(so also with one entry the data is still an array)

I would like to run a query to find the rows where the code property is an exact match (I want to avoid to use LIKE)

What I found: I cannot use OPENJSON if the column is defined as JSON, it works when the column is VARCHAR.

This database needs to be defined and data filled, so I can decide if the column type is JSON or VARCHAR.

I can also change the JSON structure, for example writing the array as value of a property.

{"mydata": [{"code": "E7", name": "Ruby Lane"}]}

Which kind of query will be the simplest to return the values I am looking for (exact match of code)?

Which kind of query is best for performance? (I saw some example with a CROSS APPLY and I think that the performance is not good)

Solution

Since you mentioned that OPENJSON works when the column is defined as VARCHAR, it is better to store your JSON data in a VARCHAR column. VARCHAR or NVARCHAR (MAX) is more flexible and works fine with JSON functions like OPENJSON.

Below is the query to find rows with an exact match of the code property:

SELECT id, json_data
FROM my_table
CROSS APPLY OPENJSON(json_data)
WITH (
    code NVARCHAR(50) '$.code',
    name NVARCHAR(50) '$.name'
) AS json_values
WHERE json_values.code = 'E7';

Results:

id  json_data
2   [{"code": "E7", "name": "Ruby Lane"}]
4   [{"code": "E7", "name": "Sarah Connor"}]

The above query will return the rows where the code property is 'E7'. The CROSS APPLY with OPENJSON function parses the JSON data and extracts the code and name properties, and the WHERE clause filters the results to find the exact match.

If you want to improve query performance, you can create a computed column and index it.

ALTER TABLE tbl1
ADD FirstCode AS JSON_VALUE(json_data, '$[0].code') PERSISTED;
CREATE INDEX IX_FirstCode ON tbl1 (FirstCode);

Reference: What's best SQL datatype for storing JSON string?