0

I have some trouble in using jsonb's like_regex statement. I usually use the statement for my jsonb's query statement, that is:

jsonb @? jsonpath

The query condition is transfered from the front as a json statement, just like:

{
"fname=":"Tiger",
"flocation~*":"Shenzhen, China, (86)"
}

I write a function to parse the json statement to the jsonpath statement, like:

jfilter:='{"fname=":"Tiger","flocation~*":"Shenzhen, China, (86)"}'::jsonb;
cur refcursor;
vkey text;
vval text;
vrule text;

open cur for select * from jsonb_each(jfilter);
loop
    fetch cur into vkey,vval;
    if found then
        if strpos(vkey,'=')>0 then
            ...
            vrule:=concat('@.',vkey,vval::text);
            ...
        elseif strpos(vkey,'~*')>0 then
            ...
            vrule:=concat('@.',vkey,' like_regex ',vval::text,' flag "i"');
            ...
        end if;
    else 
        exit;
    end if;
end loop;
close cur;

And then I get the jsonpath like:

'$[*] ? (@.fname=="Tiger" && @.flocation like_regex "Shenzhen, China, (86)" flag "i")'

When the statement contains '(' or ')', the regulation fails. In fact, for me, '(' or ')' is just a normal part of the condition. I tried to replace the '(' to '\(', but it doesn't work. I know why the statement is failed, but I don't know how to handle this kind of problem.

Pls give me some advice, thanks very much.

Jsy
  • 63
  • 7
  • Why do you want to treat the string as a regex when it isn't a regex? – Bergi Jun 25 '21 at 03:49
  • I just want to know how to ignore the special characters, treat then as normal charater. – Jsy Jun 25 '21 at 04:08
  • a) [don't use regex](https://stackoverflow.com/q/23320945/1048572) or b) [escape them](https://stackoverflow.com/a/45741630/1048572) – Bergi Jun 25 '21 at 04:22
  • Thank you, About escape, Can you give me an example? Just use the statement: '$[*] ? (@.fname=="Tiger" && @.flocation like_regex "Shenzhen, China, (86)" flag "i")'. If I want to escape '(' and ')', How to write it? Thanks very much. – Jsy Jun 25 '21 at 06:40
  • `vrule:=concat('@.',vkey,' like_regex ',f_regexp_escape(vval::text),' flag "i"');` – Bergi Jun 25 '21 at 08:17
  • thanks very much, i'll try it – Jsy Jun 25 '21 at 09:45

0 Answers0