Search code examples
sqlpostgresqljsonbjsonpath

Variable substitution for Postgres jsonpath operator like_regex?

I want to query a JSONB array using a regex filter. Following the docs, I should be able to do:

WHERE jsonb_path_exists(
  data,
  '$.value[*] ? (@ like_regex $foo flag "i")',
  jsonb_build_object('foo', 'demo')
)

But I'm getting an error:

ERROR: syntax error at or near "$foo" of jsonpath input

I think it's because $ is a valid regex character?

Here is some demo data:

INSERT INTO table_data (fields) VALUES
     ('[{"value": "Sales"}]'),
     ('[{"value": "CEO Staff"}]'),
     ('[{"value": "Procurement"'),
     ('[{"value": "CEO Staff"}]');

I wish to query all that have a value containing 'ceo'.

Solution

  • I can reproduce the problem in current Postgres 16.0. Parameter substitution works for other jsonpath operators like ==, but fails for like_regex. The manual does not seem to mention this restriction, though.

    I don't think this it related to the special meaning of $ in regexp expressions (which would be enclosed in double quotes). The error kicks in earlier. Looks like parameter substitution is just not supported there.

    Notably, the manual speaks of value ==value, but string like_regexstring (bold emphasis mine). But that lead falls flat, as there is also string starts withstring, and variable substitution works as expected there.

    There is a workaround: build a jsonpath expression dynamically and work with the associated Postgres operator @?:

    SELECT *
FROM   tbl 
WHERE  data @? format('$[*].value ? (@ like_regex %s flag "i")', '"CEO"')::jsonpath;

    fiddle

    Using (optional) format() for convenient string concatenation. The string literal '"CEO"' can be replaced by an expression - the target use case I presume?
    Then cast to jsonpath.

    The plot twist: this is superior anyway. It can use an index - as opposed to using the function jsonb_path_exists()! (And the operator @? does not allow for parameter substitution to begin with.)
    An index like (among others):

    CREATE INDEX ON tbl USING gin (data jsonb_path_ops);

    Closely related (also look at the attached fiddle over there!):

    Index usage is bound to operators internally, not functions. Some functions can be transformed by the query planner, but not in this case. Related: