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

Answer 2

The path in JSON_VALUE or JSON_QUERY can't accept wildcards or expressions. You can't write $.info.address[?(@.town='Belgrade')] to search for a specific item.

Both functions return strings too so you can't use them to extract something that can be queried. You need to convert the JSON string into rows with OPENJSON to query it, for example :

declare @json nvarchar(2000)='{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'

select *
from openjson(@json, '$.info.address')
with (town varchar(200) '$.town')
where town='Belgrade'

This uses OPENJSON to extract the contents of the address array. It extracts the town attribute as a field and then uses it in WHERE.

You can use CROSS APPLY to use OPENJSON with a table column, eg :

declare @json nvarchar(2000)='{"info":{"address":[{"town":"Belgrade"},{"town":"Paris"},{"town":"Madrid"}]}}'

DECLARE @table TABLE 
(
    id int identity primary key, 
    customer varchar(200),
    locations varchar(2000)
);

INSERT INTO @table (customer,locations)
VALUES ('AAA',@json);

SELECT *
FROM @table t CROSS APPLY OPENJSON(t.locations, '$.info.address')
                            WITH (town varchar(200) '$.town')
WHERE town='Belgrade';

Answer 3

If you stuck with JSON text is not properly formatted. Unexpected character error add SQL expression to check that the column contains valid json string. See this answer