using JSON_SEARCH to match integer in json data

Question

I have some json data in video_status_ids in mysql, I am trying to retrieve all records having 3

MySql version: 5.7.23

Demo records

[3, 6, 9]

[7, 3, 10]

Query

SELECT * FROM `videos` WHERE JSON_SEARCH(video_status_ids, "one", 3);

Resulting 0 records

Dave Stokes · Accepted Answer · 2019-02-18 14:42:55Z

2

you would be better off with JSON_CONTAINS

SQL > select * from zz where json_contains(doc,'3');
+-----+------------+
| _id | doc        |
+-----+------------+
|   1 | [3, 6, 9]  |
|   2 | [7, 3, 10] |
+-----+------------+
2 rows in set (0.0007 sec)

And lets make sure that that it acts correctly on values we do not have

SQL > select * from zz where json_contains(doc,'2');
Empty set (0.0006 sec)

answered Feb 18, 2019 at 14:42

Dave Stokes

8235 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

using JSON_SEARCH to match integer in json data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest