Menu
I have a SQL column that has a single JSON array:
{"names":["Joe","Fred","Sue"]} Given a search string, how can I use SQL to search for a match in the names array? I am using SQL 2016 and have looked at JSON_QUERY, but don't know how to search for a match on a JSON array. Something like below would be nice.
SELECT * FROM table WHERE JSON_QUERY(column, '$.names') = 'Joe' 
239
MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. As you can see ->> returns output as quoted strings, while -> returns values as they are. You can also use these operators in WHERE clause as shown below.
Getting a specific property from a JSON response object Instead, you select the exact property you want and pull that out through dot notation. The dot ( . ) after response (the name of the JSON payload, as defined arbitrarily in the jQuery AJAX function) is how you access the values you want from the JSON object.
For doing a search in a JSON array, one needs to use OPENJSON
DECLARE @table TABLE (Col NVARCHAR(MAX)) INSERT INTO @table VALUES ('{"names":["Joe","Fred","Sue"]}') SELECT * FROM @table WHERE 'Joe' IN ( SELECT value FROM OPENJSON(Col,'$.names')) or as an alternative, one can use it with CROSS APPLY.
SELECT * FROM @table CROSS APPLY OPENJSON(Col,'$.names') WHERE value ='Joe' It's very simple , can be easily done using JSON_CONTAINS() function.
SELECT * FROM table where JSON_CONTAINS(column, 'joe','$.name'); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
