How to insert variable value into JSONPath string?

I have a function:

CREATE OR REPLACE FUNCTION public.new_function1(arg1 text DEFAULT 'Miha'::text)
 RETURNS SETOF declarations
 LANGUAGE sql
 STABLE
AS $function$
select * from declarations where data @? '$.** ? (@ == $1)';
$function$

How to substitute variable arg1 for $1 in the JSONPath expression?

select * from declarations where data @? '$.** ? (@ == $1)';

If I use a string literal, all is good:

select * from declarations where data @? '$.** ? (@ == "Sergey")';

You could concatenate the function parameter into a string and cast to jsonpath (as suggested in comments). But nested string values are double-quoted in a jsonpath expression (while Postgres string types use single-quotes). So neither format() nor any of the quote_*() family of functions handles that properly.

For string-concatenation, you could double-quote manually, escaping nested double-quotes if any. Or you cast to json / jsonb and back to text to make Postgres do the work for you, reliably. Either with jsonb_build_object('arg1', arg1) to build the jsonb argument for the function call. Or with to_json(arg1)::text for string concatenation.

Function jsonb_path_exists()

The largely equivalent function jsonb_path_exists() offers the option to pass parameters to the jsonpath expression. That's not implemented for the operator @?. A clean & safe way is to use jsonb_path_exists()

CREATE OR REPLACE FUNCTION public.new_function1(arg1 text DEFAULT 'Miha')
  RETURNS SETOF public.declarations       -- schema-qualify to be safe
  LANGUAGE sql STABLE PARALLEL SAFE AS    -- PARALLEL SAFE in Postgres 9.6 or later
$func$
SELECT *
FROM   public.declarations
WHERE  jsonb_path_exists(target => data
                       , path   => 'strict $.** ? (@ == $arg1)'      -- can use a param ...
                       , vars   => jsonb_build_object('arg1', arg1)  -- ... which is defined in next arg
                       , silent => true);                            -- to behave exactly like original operator @?
$func$;

Using the optional, more verbose function call with named parameters for clarity. See:

• https://stackoverflow.com/questions/16782199/functions-with-variable-number-of-input-parameters/16789179#16789179

Major downside: Postgres indexes are bound to operators, not functions. So this form gets no index support. No good for big tables. Related:

• https://stackoverflow.com/questions/4058731/can-postgresql-index-array-columns/29245753#29245753

Operator @?

CREATE OR REPLACE FUNCTION public.new_function1(arg1 text DEFAULT 'Miha')
  RETURNS SETOF public.declarations
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT *
FROM   public.declarations
WHERE  data @? format('strict $.** ? (@ == $s)'  -- note: strict
                    , to_json(arg1)::text        -- safe conversion
                     )::jsonpath;
$func$;

I added strict as advised in the manual:

> To avoid surprising results, we recommend using the .** accessor only in the strict mode.

Wouldn't break in this particular case, but it's still cheaper.

You could also use dynamic SQL with EXECUTE in a PL/pgSQL function and pass the jsonpath expression in a USING clause. (The same issue with double-quoting applies.) See:

• https://stackoverflow.com/questions/14065271/how-to-use-execute-format-using-in-postgres-function/14066715#14066715
• https://stackoverflow.com/questions/22656716/format-specifier-for-integer-variables-in-format-for-execute/22675836#22675836

Index

CREATE INDEX declarations_gin_idx ON public.declarations USING gin (data);

Important detail for your particular jsonpath expression, quoting the manual:

> The jsonb_ops operator class also supports .* and .** accessors,
> but the jsonb_path_ops operator class does not.

So you cannot use the more optimized operator class jsonb_path_ops for this. See:

• https://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array/18405706#18405706