CODEWITHSUNDEEP
mysqljsonregexexpressionstrongloop
Riaz
Riaz

Reputation: 561

How to search JSON data in mysql using Regular expression

I am new to regular expression, I have stored JSON data in table. I want to fetch data based on userId, userId is a property of Users Object.

Example: I want fetch all the records if users contain 513

My table data sample:

 Table name:share

Id  name            sharedusers
1   xxxx            {"accessType":0,"permission":"1","specificUsers":
                    [{"users":"502,512,513","permission":"1"}]}

2   yyy             {"accessType":0,"permission":"1","specificUsers":
                    [{"users":"47,52,60","permission":"1"}]} 

3   zzzz            {"accessType":0,"permission":"1","specificUsers":
                    [{"users":"502,512,513","permission":"1"}]}

I have tried this code but I am getting all the records, filter is not applied

SELECT * FROM share where sharedusers  REGEXP '"users":"[[:<:]]513[[:>:]]"';

Please give any sample.

Upvotes: 1

Views: 6223

Answers (2)

Rick James
Rick James

Reputation: 142356

Upgrade to MySQL 5.7 and use the builtin functions for interrogating JSON columns.

Upvotes: 1

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627044

You can use

REGEXP '"users":"[^"]*[[:<:]]513[[:>:]]'

Here, [^"]* (0+ characters other than ") is added and final " is removed to effectively match 513 as a whole word inside a pair of quotes that enclose the value.

Upvotes: 4

Related Questions