Question 267771 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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'.

Tags:

2

Answers


  1. 0

    I assume that the fields JSON array might contain more than one element and therefore I would first flatten using lateral join and then filter using the case-insensitive ‘regexp-match’ operator ~*.

    select fields, f -- or whatever else you might need
from table_data
cross join lateral jsonb_array_elements(fields) f
where f->>'value' ~* 'yCEOy';

    If fields JSON array always contains one element then simply

    select fields from table_data
where fields->0->>'value' ~* 'yCEOy';

    DB Fiddle demo

    Login or Signup to reply.
  2. 0

    I can reproduce the problem. 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.

    There is a workaround: build a jsonpath expression dynamically:

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

    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()! See:

    fiddle

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search