CODEWITHSUNDEEP
mysqljson
Prafulla Kumar Sahu
Prafulla Kumar Sahu

Reputation: 9703

Check json_contains on json of json in mysql table

I have json data in my table which contains again json value like below sample data

This is laravel audits, audits table new_values column ( type: text ) record but for this particular case I am not using eloquent or query builder, it is just a raw query

{"video_status_ids":"[6, 8, 14, 29]"}

now I want to check if this has 8 in it.

I tried

select * from audits WHERE JSON_CONTAINS(new_values, '8', '$.video_status_ids')

and

JSON_CONTAINS(audits.new_values, CAST(' . $status . ' AS json), "$.video_status_ids")';

I tried json_Search to

  1. select * from audits WHERE JSON_SEARCH(new_values, 'all', '8', 'video_status_ids', '$[*]');

  2. select * from audits WHERE JSON_SEARCH(new_values, 'all', '8', NULL, '$[video_status_ids.*]');

  3. SELECT * FROM audits WHERE JSON_SEARCH(new_values, 'all', '8', NULL, '$.video_status_ids.*')

Getting 0 records

and some other queries, but could not get the expected result.

by refering this but I am getting 0 records on it.

Upvotes: 0

Views: 2337

Answers (2)

Nick
Nick

Reputation: 147236

Your problem is that the "array" in your JSON data is actually a string (note the double quotes around it). So to search inside it, you first need to JSON_EXTRACT and then JSON_UNQUOTE it:

SELECT JSON_CONTAINS(JSON_UNQUOTE(JSON_EXTRACT(new_values, '$.video_status_ids')), '8')
FROM audits

You can also use the shorthand ->> for JSON_UNQUOTE(JSON_EXTRACT()):

SELECT JSON_CONTAINS(new_values->>'$.video_status_ids', '8')
FROM audits

Demo on dbfiddle

Working Solution

SELECT * FROM audits WHERE JSON_CONTAINS(new_values->>'$.video_status_ids', '8')

Upvotes: 1

Daniel
Daniel

Reputation: 11182

If you check out the documentation for JSON_CONTAINS:

Indicates by returning 1 or 0 whether a given candidate JSON document is contained within a target JSON document

It can only check for existance of a key - not search an array.

The JSON_SEARCH() function however:

Returns the path to the given string within a JSON document

This is what you need. Remember to pass your path as the last argument, since the array is not the root element.

Database.guide has a really good article about using JSON_SEARCH for your purpose (including syntax help and examples):

https://database.guide/json_search-find-the-path-to-a-string-in-a-json-document-in-mysql/

Upvotes: 0

Related Questions