How to insert variable value into JSONPath string?

Question

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")';

Answer 1

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:

• Functions with variable number of input parameters

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

• Can PostgreSQL index array columns?

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:

• How to use EXECUTE FORMAT ... USING in Postgres function
• Format specifier for integer variables in format() for EXECUTE?

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:

• Index for finding an element in a JSON array