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;