question

E.Thompson avatar image
E.Thompson asked

Find records where JSON contains value

I have been trying to figure out how to find records in a table with a JSON column where the JSON contains a particular value and it I just cannot seem to find the right answer. I find a lot of examples where the json is present in a variable but not where you search a SQL table to find matches for values within the JSON.

Using this example how would I find all records with a match?

CREATE TABLE testTable (RowId INTEGER IDENTITY(-2147483648, 1) NOT NULL, JSONString varchar(max) NOTNULL, createdOn DATETIME NOTNULL);

INSERT INTO testTable(JSONString, createdOn)

VALUES('{"Sales": [

{"saleDate": "01/01/2022","manufacturer": "Toyota", "model": "Camry", "amount": "16,540","year": 2015}, {"saleDate": "01/01/2022","manufacturer": "Volkswagen", "model": "Golf", "amount": "10,207", "year": 2012}]}', '01/02/2022 21:02:44.323'),

('{"Sales": [

{"saleDate": "01/02/2022","manufacturer": "Toyota", "model": "Tundra", "amount": "14,609","year": 2014}, {"saleDate": "01/02/2022","manufacturer": "Volkswagen", "model": "Golf", "amount": "11,672", "year": 2013}]}', '01/02/2022 21:01:16.251' ),

('{"Sales": [

{"saleDate": "01/03/2022","manufacturer": "Audi", "model": "TT", "amount": "10,421","year": 2010}, {"saleDate": "01/03/2022","manufacturer": "BMW", "model": "320i", "amount": "16,990", "year": 2014}]}', '01/03/2022 21:04:33.875' )

, ('{"Sales": [

{"saleDate": "01/04/2022","manufacturer": "Volvo", "model": "S60", "amount": "19,461","year": 2017}, {"saleDate": "01/04/2022","manufacturer": "Volkswagen", "model": "Jetta", "amount": "11,221", "year": 2013}]}', '01/04/2022 21:05:12.982' )

, ('{"Sales": [

{"saleDate": "01/05/2022","manufacturer": "Toyota", "model": "Highlander", "amount": "22,540","year": 2017}, {"saleDate": "01/05/2022","manufacturer": "Chevy", "model": "Volt", "amount": "23,954", "year": 2018}]}', '01/05/2022 21:01:26.562' );

How would I find all the records when Volkswagens were sold?

This returns an empty result:

SELECT tt.*

FROM dbo.testTable AS tt

WHERE JSON_QUERY(tt.JSONString, '$.Sales.manufacturer') = 'Volkswagen';




sqlserverjson
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

If the JSON was as simple as that example and only ever contained 2 elements in the array, then you can easily do this

SELECT tt.*
FROM dbo.testTable AS tt
WHERE JSON_value(tt.JSONString, '$.Sales[0].manufacturer') = 'Volkswagen'
OR JSON_value(tt.JSONString, '$.Sales[1].manufacturer') = 'Volkswagen';


But that's no good for a dynamic amount of elements. You would have to iterate over the elements in the array and check those. I use my own function for this: JSONTABLE (https://rileywaterhouse.co.uk/jsontable/)

select tt.* 
FROM dbo.testTable AS tt
cross apply JSONTable(tt.JSONString)
where [name] = 'manufacturer' and [Value] = 'Volkswagen'


A native way would be to do that iteration yourself, and as long as the schema was consistent, you could do

select tt.*
from testtable AS tt
cross apply openjson(JSONString) as J1
cross apply openjson(J1.Value) with ( Manufacturer nvarchar(max) '$.manufacturer' ) J2
where J2.Manufacturer = 'Volkswagen'


1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for leading me in the right direction with the JSONTable. I found ParseJSON and UnwrapJSON which did the trick for me.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.