CODEWITHSUNDEEP
mysqlregex
be well
be well

Reputation: 335

Regex match complex substring

I have a json stored in MySQL and I want to find matches using regex. I was trying to achieve this by building a query with REGEXP instead of LIKE.

{"type":"4","Record channels HDCVI":{"1":"4","3":"16"},
"Supported standarts HDCVI DVR":{"0":"HDCVI","2":"IP","3":"AHD","4":"Analog"},
"Record resolution HDCVI DVR":{"1":"FullHD 1080p"},
"HDD amount":{"1":"2","2":"4"},
"Specs HDCVI DVR":{"0":"2x HDMI","1":"TV-out","2":"Videoanalytics","3":"eSATA","5":"RS-485"}}

For example, I need to get all the records with 4 Record channels HDCVI.

So, my idea is to look for a substring like this:

"Record channels HDCVI":{" ... :"4" ... "}

and instead of three dots there should be placeholders like any amount of any characters BUT this expression should stop with the first match of closing curly bracket.

This selects substring till the last 4"} under HDD amount section. Can't figure it out.

/Record channels HDCVI.+"4"}/g

Upvotes: 1

Views: 128

Answers (2)

Brian Stephens
Brian Stephens

Reputation: 5261

You should do this:

/"Record channels HDCVI:{[^}]+:"4"/g

Upvotes: 2

Zak
Zak

Reputation: 1100

You should be able to do something like this:

SELECT * WHERE field REGEXP '"Record channels HDCVI":{[^}]*"4"';

However, I can't imagine that it will be particularly fast across a lot of records.

Anyhow, the important bit is {[^}]*"4", which breaks down to:

{     -- match the opening brace
[^}]* -- match 0 or more characters that are not a closing brace
"4"   -- match literal "4"

If a closing brace occurs before the "4" then the pattern won't match.

Upvotes: 1

Related Questions