I am trying to do a case-insensitive partial search (contains string) on a property value - stored in a jsonb field in postgres.
The search is looking for a value within the title column of table destination which has an array of elements as follows:
[{"key": "EN", "text":"london and milk"},{"key": "FR", "text":"Edinburgh with milk and honey"}]
I have created a GIN index on the title field and a function to deal with the search.
CREATE OR REPLACE FUNCTION search(query_string character varying)
RETURNS SETOF destination
LANGUAGE 'plpgsql'
AS $BODY$
begin
return query select *
from destination
--where title @? '$.* ? (@ like_regex ' || query_string || ' flag "i")';
where title @? '$.* ? (@ like_regex ".*milk.*" flag "i")';
end;
$BODY$;
So the function works nicely if the regexp string is hardcoded (as shown above), but the search should be based on the incoming query_string. The commented line in the function shows an attempt to try to include the parameter in the query. (this will result in unterminated string constant error)
How can I exchange the hard-coded milk to parameter search_query?
Are there other (simpler) ways that would yield the same end result?
Your problem is one of precedence. @? and '||' are tied and are processed left to right, so you are applying @? to only a fragment of the string not the completely built string. Then you are trying to concat things to the Boolean result of @?. You can fix this by constructing the string inside parentheses. A side affect of this is that you then have to cast it to jsonpath explicitly.
where title @? ( '$.* ? (@ like_regex "' || query_string || '" flag "i")' )::jsonpath;
But I think it would be cleaner to construct the jsonpath in a variable, rather than on the fly in the query itself. Could someone inject something into the jsonpath string that could do something nasty? I don't know enough about jsonpath to rule that out.
(code part of the suggested solution edited by question author to include the double quotes missing - see comment)