Sample Header Ad - 728x90

Postgres like_regex with variables

0 votes
2 answers
927 views
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'.
Asked by tdranv (109 rep)
Oct 18, 2023, 02:37 PM
Last activity: Oct 18, 2023, 07:21 PM