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
Last activity: Oct 18, 2023, 07:21 PM