How to regexp in MySql json string

Question

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!

try SELECT _u.id FROM users _u WHERE _u.info like '"136"'

maha
– maha

2020-07-22 06:19:43 +00:00
Commented Jul 22, 2020 at 6:19 — maha
– maha, Commented Jul 22, 2020 at 6:19
Why don't you use myaql's json functions instead?

Shadow
– Shadow

2020-07-22 06:19:56 +00:00
Commented Jul 22, 2020 at 6:19 — Shadow
– Shadow, Commented Jul 22, 2020 at 6:19
@maha It might work but considering accuracy...

dovstone
– dovstone

2020-07-22 06:54:51 +00:00
Commented Jul 22, 2020 at 6:54 — dovstone
– dovstone, Commented Jul 22, 2020 at 6:54

Tim Biegeleisen · Accepted Answer · 2020-07-22 07:05:07Z

2

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

edited Jul 22, 2020 at 7:05

answered Jul 22, 2020 at 6:21

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

6 Comments

dovstone Over a year ago

Mysql has thrown this error: FUNCTION dev.JSON_SEARCH does not exist

Tim Biegeleisen Over a year ago

What is your MySQL version? If you're using a very old version, then it may not have full JSON support. In that case, and assuming you expect a need to work with JSON, then consider upgrading to MySQL 8+.

dovstone Over a year ago

5.0.12 in local but even my prod hosting has thrown the same error.

Tim Biegeleisen Over a year ago

It is running in the demo, this is all I can observe here.

Tim Biegeleisen Over a year ago

@dovstone OK I have given you an option using REGEXP, but note carefully that in general searching JSON content using regular expression is generally a very bad idea. If your JSON to search is anything other than what you showed us, REGEXP could backfire and return false positives.

|

Collectives™ on Stack Overflow

How to regexp in MySql json string

1 Answer 1

Demo

Demo

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related