Search json array using SQL server JSON_VALUE

Question

Assuming a given json object:

{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}

SQL server has JSON_VALUE function that can be used in WHERE clause to match against a particular json element e.g.

WHERE JSON_VALUE(columnName, $.info.address[1].town) = 'Belgrade'

The issue is that it requires an index. How do I search in all array elements specified by the path?

Answer 1

You may try to use OPENJSON and a WITH clause (to specify columns and their types), and then search in all elements:

-- JSON
DECLARE @json nvarchar(max)
SET @json = N'{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'

-- Statement
SELECT Town
FROM OPENJSON(@json, '$.info.address') WITH (
   Town nvarchar(100) '$.town'
)
WHERE Town = N'Belgrade'

If the JSON content is stored in a table column, next approach is also an option:

-- Table
CREATE TABLE #Data (
   Id int,
   JsonData varchar(max)
)
INSERT INTO #Data
   (Id, JsonData)
VALUES 
   (1, N'{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'),
   (2, N'{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'),
   (3, N'{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}')

-- Statement
SELECT DISTINCT d.Id
FROM #Data d
CROSS APPLY OPENJSON(d.JsonData, '$.info.address') WITH (
   Town nvarchar(100) '$.town'
) j   
WHERE (j.Town = N'Belgrade') OR (j.Town = N'Paris')

Result:

-------
Id
-------
1
2
3