Is there a way how to compare array from JSON field against JSON_ARRAY in MySQL?

user_1307991 0
Viewed 609

MySQL table contacts

I have a JSON type column responses, which content is

{"1": ["Yes", "No", "Maybe"], "2": ["Yes"], "3": ""}

I would like to know if it's possible in SELECT statement -> WHERE clause to find out if first JSON element is equal with some given array (it would change).

My idea was something like this:

WHERE contacts.responses->>'$."1"' = JSON_ARRAY('Yes','No','Maybe')

but this doesn't work (even though when both comparison sides included in SELECT statement, they are displayed equally - '["Yes", "No", "Maybe"]'). So, is there any other way how to accomplish this?

mysql-8.0 mysql arrays sql json
1 Answers

There are a plenty of functions you can work in Mysql 8 The JSON Data Type

And you can use JSON_EXTRACT to extract your data from your json in the column. Your data you entered in the question is object and you must use $."1" to point it. Be careful for array you must use $[1].

The following is an example of your data and your probably output.

CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"1": ["Yes", "No", "Maybe"], "2": ["Yes"], "3": ""}');
INSERT INTO t1 VALUES('{"1": ["YY", "XX", "Maybe"], "2": ["Yes"], "3": ""}');
INSERT INTO t1 VALUES('{"1": ["Yes", "Maybe"], "2": ["Yes"], "3": ""}');

select jdoc from t1 where JSON_EXTRACT(jdoc,'$."1"') = JSON_ARRAY('Yes','No','Maybe');
user_25274580
Related