1

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
3
  • What is $foo supposed to mean? Perhaps you want to include some sample data and the desired output. Commented Oct 18, 2023 at 15:01
  • $foo is a variable placeholder. Please see the docs for jsonb_path_exists. I will include demo data now. Commented Oct 18, 2023 at 15:02
  • Duplicate of stackoverflow.com/questions/77317468/… Commented Oct 18, 2023 at 19:50

2 Answers 2

Reset to default
1

You cannot use a variable for the pattern, as the documentation says:

Keep in mind that the pattern argument of like_regex is a JSON path string literal

Improve this answer
4
  • 1
    It is far from clear that being a JSON path string literal means substitution can't/shouldn't work. Commented Oct 18, 2023 at 19:43
  • 1
    That remark does not seem to explain why parameter substitution fails. The same works for starts with, which (according to the manual) also expects a string. I answered a question duplicate on SO (before noticing the dupe), and became convinced this is a bug. I filed a report accordingly: postgresql.org/message-id/… Commented Oct 18, 2023 at 19:43
  • @ErwinBrandstetter I may have interpreted more into it than was intended, granted. On the other hand, this is the only appearance of the word "literal" on the page, and I was reminded of cases like INTERVAL '3' DAYS where the string must be a literal and cannot be an expression. Commented Oct 18, 2023 at 20:26
  • Either it's a bug, or the manual could profit from clarification. Either way, I don't see a reason why parameter substitution couldn't work. Commented Oct 18, 2023 at 20:59
0

As Laurenz's answer states you can't use variable in that context. However you can use the workaround below if it fits with your case.

Use format function which returns text. Then cast to jsonpath

CREATE TABLE table_data (fields jsonb, search_field text);
INSERT INTO table_data (fields, search_field) VALUES
     ('[{"value": "Sales"}]', 'SSS'),
     ('[{"value": "CEO Staff"}]', 'sta'),
     ('[{"value": "Procurement"}]', 'CURE'),
     ('[{"value": "CEO Staff"}]', 'ceo');


SELECT
  jsonb_path_query_first  ( 
  td.fields, 
  format('$.value[*] ? (@ like_regex "%s" flag "i")', td.search_field)::jsonpath
  )
FROM table_data td
jsonb_path_query_first
null
"CEO Staff"
"Procurement"
"CEO Staff"
Improve this answer

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.