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!
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"')
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.
Returns 0.
精彩评论