CODEWITHSUNDEEP
mysqlregexjson
mrflos
mrflos

Reputation: 13

MYSQL REGEXP search in JSON string

I'm a beginner in regexp and i try to search in json formatted text, but i cannot make it work right:

SELECT DISTINCT tag, body FROM pages 
WHERE (body REGEXP BINARY '"listeListeOuiNon":".*1.*"')

It shows me as results text with

"listeListeOuiNon":"1" and

"listeListeOuiNon":"1,2" and

"listeListeOuiNon":"0,1" as expected,

but also "listeListeOuiNon":"2" (not expected)

Any idea? Maybe it's because it's greedy, but i'm not sure...

Thanks in advance!

Upvotes: 1

Views: 2680

Answers (3)

Jauzsika
Jauzsika

Reputation: 3251

Well, it's quite easy to debug:

SELECT '"listeListeOuiNon":"2"' REGEXP BINARY '"listeListeOuiNon":".*1.*"'

returns 0

SELECT '"listeListeOuiNon":"1"' REGEXP BINARY '"listeListeOuiNon":".*1.*"'

returns 1

SELECT '"listeListeOuiNon":"1,2"' REGEXP BINARY '"listeListeOuiNon":".*1.*"'

returns 1

So something is not right at your side... because it just could not return rows where body equals "listeListeOuiNon":"2". But it is possible, that body has several of these statements, something like:

body => '"listeListeOuiNon":"1,2", "listeListeOuiNon":"2"'

So you have to modify your regexp:

'^"listeListeOuiNon":".*1.*"$'

Well, then you have to modify your query:

SELECT DISTINCT tag, body FROM pages WHERE (body REGEXP BINARY '"listeListeOuiNon":".*1.*"') AND NOT (body REGEXP BINARY '"listeListeOuiNon":"2"')

Upvotes: 2

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36641

Returns 0.

enter image description here

Upvotes: 1

Romain
Romain

Reputation: 12819

I would try to replace the two .* with [^"]*... That'll however only be sufficient if your listeListeOuiNon cannot contain litteral "s, or you'd have to also handle the escape sequence. Basically with the . you'll match any JSON string that has a 1 "after" "listListOuiNon":", even if it's in another field, and yes, that's because it's greedy.

Upvotes: 1

Related Questions