3

I'm trying to construct a json/sql path query dynamically inside a procedure.

profile @? '$.timezone ? (@ like_regex "auck" flag "i")';

In the above I want timezone and auck to be constructed dynamically.

So far I have the following

end_user.profile @? format('$.%I ? (@ like_regex "%I" flag "i")', condition.field, replace(replace(condition.value,'\', '\\'),'"','\"'))::jsonpath

due to the need to wrap in quotes and escape the regex it doesn't look like the most elegant code.

A straight string match looks like this

end_user.profile @? format('$.%I ? (@ == "%s")',condition.field, replace(condition.value,'"','\"'))::jsonpath

I'm hoping there is a better & safer way to do this.

I'm trying to update some code that works in a similar fashion without using json/sql which I've included a snipped of below.

create function private.end_user_profile_filter(end_user private.end_user, condition private.profile_filter)
returns boolean as $$
  select    case
    when condition.condition = 'REGEX_MATCH'
        then end_user.profile->>condition.field ~* condition.value
    else
      end_user.profile->>condition.field = condition.value
 end;
$$ language sql stable;
Tim
  • 4,471
  • 5
  • 36
  • 42

0 Answers0