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.

6
  • Why do you want to treat the string as a regex when it isn't a regex? Commented Jun 25, 2021 at 3:49
  • I just want to know how to ignore the special characters, treat then as normal charater. Commented Jun 25, 2021 at 4:08
  • a) don't use regex or b) escape them Commented Jun 25, 2021 at 4: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. Commented Jun 25, 2021 at 6:40
  • vrule:=concat('@.',vkey,' like_regex ',f_regexp_escape(vval::text),' flag "i"'); Commented Jun 25, 2021 at 8:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.