CODEWITHSUNDEEP
mysqljsonregex
dovstone
dovstone

Reputation: 144

How to regexp in MySql json string

Let's assume this users table:

-----------------------------------------
| id | ... | info                       |
-----------------------------------------
| 1  | ... | {"items":["132","136"]}    |

I need to make a request to fetch users that have items with id == 136.
This following is the sql I built but it does not work and I dont understand why:

SELECT _u.id FROM users _u WHERE _u.info REGEXP '("items":)([)("136")(])'

Thank you in advance!

Upvotes: 1

Views: 954

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

Here is one approach using the MySQL JSON functions:

SELECT *
FROM yourTable
WHERE JSON_SEARCH(JSON_EXTRACT(json, "$.items"), 'one', "136") IS NOT NULL;

Demo

The call to JSON_EXTRACT first extracts the JSON array under the items key. Then, we use JSON_SEARCH to try to find an element "136".

Edit:

If you are certain that the JSON to be searched would always just be one key items along with a single level JSON array, then REGEXP might be viable here:

SELECT *
FROM yourTable
WHERE json REGEXP '"items":\\[.*"136".*\\]';

Demo

Upvotes: 2

Related Questions