5

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

Improve this question
1
  • where fields->0->>'value' ~* 'ceo'; Commented Oct 18, 2023 at 16:53

2 Answers 2

Reset to default
6

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:

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Let me just preface this by saying that it's an honour to receive an answer like this. Thank you, sincerely. And yes, what I'm trying to do is build a parameterised query. I posted the same question on the DB Admins stack exchange and found out that variables can't be used in this context (more here), accompanied with a suggestion, similar to yours. Thank you once again.
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' ~* '\yCEO\y';

If fields JSON array always contains one element then simply

select fields from table_data
where fields->0->>'value' ~* '\yCEO\y';

DB Fiddle demo

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.