Check json_contains on json of json in mysql table

Question

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

select * from audits WHERE JSON_SEARCH(new_values, 'all', '8', 'video_status_ids', '$[*]');
select * from audits WHERE JSON_SEARCH(new_values, 'all', '8', NULL, '$[video_status_ids.*]');
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.

Prafulla Kumar Sahu · Accepted Answer · 2019-03-20 11:04:00Z

1

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')

edited Mar 20, 2019 at 11:04

Prafulla Kumar Sahu

9,75911 gold badges78 silver badges116 bronze badges

answered Mar 20, 2019 at 7:53

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Prafulla Kumar Sahu Over a year ago

I am getting a set of nulls and 0s for both of these queries.

Nick Over a year ago

@PrafullaKumarSahu Just realised I used new_value instead of new_values did you fix that?

Prafulla Kumar Sahu Over a year ago

Also, that is json inside json not array in side json.

Nick Over a year ago

@PrafullaKumarSahu how is your data different from my demo? It works fine there...

Nick Over a year ago

@PrafullaKumarSahu regardless of it being an array or JSON (in this case they are the same) because it is quoted you need to extract it and unquote before you can search on it.

|

Daniel · Accepted Answer · 2019-03-20 07:34:53Z

0

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/

answered Mar 20, 2019 at 7:34

Daniel

11.3k12 gold badges56 silver badges94 bronze badges

3 Comments

Prafulla Kumar Sahu Over a year ago

As you suggested, I tried using JSON_SEARCH, but getting error, I have edited my question,m could you please look into it once?

Prafulla Kumar Sahu Over a year ago

I was using wit wrong, so corrected it, but still getting 0 records.

Nick Over a year ago

This is incorrect. Try SELECT JSON_CONTAINS('[6, 8, 14, 29]', '8') and you will get 1 (true).

Collectives™ on Stack Overflow

Check json_contains on json of json in mysql table

2 Answers 2

10 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related