How to search JSON data in mysql using Regular expression

Question

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.

Wiktor Stribiżew · Accepted Answer · 2016-03-04 14:06:00Z

4

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.

answered Mar 4, 2016 at 14:06

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

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

7 Comments

Riaz Over a year ago

how to convert this ( '"users":"[^"]*[[:<:]]513[[:>:]]' )RegEx to String defining a regular expression (for example, '^foo' ). Regular expression literal (for example, /^foo/ ). Regular expression object (for example, new RegExp(/John/)).

Riaz Over a year ago

if you need more details please look this link docs.strongloop.com/display/public/LB/…

Wiktor Stribiżew Over a year ago

You mean you need a JavaScript compatible regex? Use /"users":"[^"]*\b513\b/

Riaz Over a year ago

@Thanks for your response. I tried in node.js but still I am getting error. can you give same(/"users":"[^"]*\b513\b/) in string defining format?

Riaz Over a year ago

I tried in javascriipt but I am getting undefined. var patt = new RegExp(/"users":"[^"]*\b513\b/); var json= {"accessType":0,"permission":"1","specificUsers": [{" users":"502,512,513","permission":"1"}]}; var res = patt.json;

|

Rick James · Accepted Answer · 2016-03-06 04:53:57Z

1

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

answered Mar 6, 2016 at 4:53

Rick James

144k15 gold badges144 silver badges255 bronze badges

4 Comments

Riaz Over a year ago

Thanks for your response. I will try.

John Over a year ago

Though, those functions don't include regex support as far as I recall. Including mysql 8

Rick James Over a year ago

@John - Use the Json functions instead of trying parse with a REGEXP.

John Over a year ago

@RickJames It's not a real solution, JSON_SEARCH and similar are by magnitudes less flexible than regular expressions. In my specific case I have hundreds of regex codes with complex content already existing, I can't switch. So I have to treat the json as text and search it that way which likely introduces a few problems due to escaping etc.

Collectives™ on Stack Overflow

How to search JSON data in mysql using Regular expression

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related