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.

score 1 · Accepted Answer · edited Mar 20 '19 at 11:04

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 '19 at 11:04

Prafulla Kumar Sahu

9,321
11
68
105

answered Mar 20 '19 at 07:53

Nick

138,499
22
57
95

I am getting a set of `null`s and `0`s for both of these queries. – Prafulla Kumar Sahu Mar 20 '19 at 07:56
@PrafullaKumarSahu Just realised I used `new_value` instead of `new_values` did you fix that? – Nick Mar 20 '19 at 07:57
Also, that is json inside json not array in side json. – Prafulla Kumar Sahu Mar 20 '19 at 07:57
@PrafullaKumarSahu how is your data different from my demo? It works fine there... – Nick Mar 20 '19 at 07:58
@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. – Nick Mar 20 '19 at 07:59
Your data seems same as mine, and I just copied and pasted in phpmyadmin console, let me check what is the difference in data. – Prafulla Kumar Sahu Mar 20 '19 at 08:02
I just checked new_values column type is text for me, is this the problem? – Prafulla Kumar Sahu Mar 20 '19 at 08:03
@PrafullaKumarSahu it works fine as text in the demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0432a9caadb8881d9acd1b0ad4d6c70c What version of MySQL are you using? – Nick Mar 20 '19 at 08:06
I have added sample data in a gist [here](https://gist.github.com/PrafullaKumarSahu/0fe5b4d01cad288e948f34e440108594) Can you please take a look at it? – Prafulla Kumar Sahu Mar 20 '19 at 08:12
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190348/discussion-between-prafulla-kumar-sahu-and-nick). – Prafulla Kumar Sahu Mar 20 '19 at 08:15

score 0 · Answer 2 · answered Mar 20 '19 at 07:34

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 '19 at 07:34

Daniel

10,641
12
47
85

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 Mar 20 '19 at 07:47
I was using wit wrong, so corrected it, but still getting 0 records. – Prafulla Kumar Sahu Mar 20 '19 at 07:50
This is incorrect. Try `SELECT JSON_CONTAINS('[6, 8, 14, 29]', '8')` and you will get 1 (true). – Nick Mar 20 '19 at 07:55

Check json_contains on json of json in mysql table

2 Answers2