0

In my sql code, I receive a input string as a regular match's filter, I want to have the whole string as a normal string, even it includes some special characters.

Just look below:

do $$ declare 
    jdata jsonb='[{"name":"Dog 3*240+1*120"}]'::jsonb;
    vfilter1 text='dog';
    vfilter2 text='3*240+1*120';
    vexists bool=false;
begin 
    select jdata @? concat('$[*] ? (@.name like_regex "',vfilter1,'" flag "i")')::jsonpath into vexists;
    raise notice 'exists:%',vexists; --the result is true
    select jdata @? concat('$[*] ? (@.name like_regex "',vfilter2,'" flag "i")')::jsonpath into vexists;
    raise notice 'exists:%',vexists;-- the result is false
end;
$$ language plpgsql;

the sql code

the string 3*240+1*120 include + and * characters, perhaps this causes the regular match have them as special character. In my code, I just want to have the whole vfilter string includes all special characters together as a normal string for the regular match.

What should I do?

Jsy
  • 63
  • 7
  • 1
    Why are you using like_regex or anything related to regex? – Bohemian Nov 21 '21 at 05:25
  • Because the json structure is nested, that is, the array is contains in another array, use like_regex, I can judge easily by jsonpath $.array1[*].array2[*] ? (@.key like_regex "xxx"). – Jsy Nov 21 '21 at 05:38
  • 1
    "*perhaps this causes the regular match have them as special character*" - yes it does. See [here](https://stackoverflow.com/q/5144036/1048572) for a solution. Also use `to_json()` to get a json string from text, not concatenation with `"`. – Bergi Nov 21 '21 at 06:48
  • I tried `select '[{"name":"Dog 3*240+1*120"}]'::jsonb @? '$[*] ? (@.name like_regex "3\*240\+1\*120" flag "i")'`, returns false. Why? What should I do? – Jsy Nov 21 '21 at 07:43

1 Answers1

3

You should read the documentation for the feature you are using.

The optional flag string may include one or more of the characters i for case-insensitive match, m to allow ^ and $ to match at newlines, s to allow . to match a newline, and q to quote the whole pattern (reducing the behavior to a simple substring match).

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thanks a lot. I solved my trouble. You are really warm-hearted and professional, Thanks! – Jsy Nov 25 '21 at 01:11