Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (id int, data jsonb);

INSERT INTO tbl VALUES
  (1, '[{"value": "Sales"}]')
, (2, '[{"value": "CEO Staff"}]')
, (3, '[{"value": "Procurement"}]')
, (4, '[{"value": "CEO Staff"}]')
;

CREATE TABLE

INSERT 0 4

-- works for ==
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data, '$[*].value ? (@ == $foo)', '{"foo": "CEO Staff"}');

id
data

2
[{"value": "CEO Staff"}]

4
[{"value": "CEO Staff"}]

SELECT 2

-- FAILS for "ilike_regex"
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data, '$[*].value ? (@ like_regex $foo)', '{"foo": "CEO"}');

ERROR:  syntax error at or near "$foo" of jsonpath input
LINE 4: WHERE  jsonb_path_exists(data, '$[*].value ? (@ like_regex $...
                                       ^

-- notably, parameter substitution works for "starts with"!
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data, '$[*].value ? (@ starts with $foo)', '{"foo": "CEO"}');

id
data

2
[{"value": "CEO Staff"}]

4
[{"value": "CEO Staff"}]

SELECT 2

-- cross-check: works without parameter substitution
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data, '$[*].value ? (@ like_regex "CEO")');

id
data

2
[{"value": "CEO Staff"}]

4
[{"value": "CEO Staff"}]

SELECT 2

-- superior workaround: this can use an index!
SELECT *
FROM   tbl 
WHERE  data @? format('$[*].value ? (@ like_regex %s flag "i")', '"CEO"')::jsonpath;

id
data

2
[{"value": "CEO Staff"}]

4
[{"value": "CEO Staff"}]

SELECT 2

-- applicable index (!)
CREATE INDEX ON tbl USING gin (data jsonb_path_ops);
-- index usable?
SET enable_seqscan = off;

CREATE INDEX

SET

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

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=17.30..21.32 rows=1 width=36)

  Recheck Cond: (data @? (format('$[*].value ? (@ like_regex %s flag "i")'::text, '"CEO"'))::jsonpath)

  ->  Bitmap Index Scan on tbl_data_idx  (cost=0.00..17.30 rows=1 width=0)

        Index Cond: (data @? (format('$[*].value ? (@ like_regex %s flag "i")'::text, '"CEO"'))::jsonpath)

EXPLAIN

-- NO
EXPLAIN
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data, '$[*].value ? (@ == $foo)', '{"foo": "CEO Staff"}');

QUERY PLAN

Seq Scan on tbl  (cost=10000000000.00..10000000001.05 rows=1 width=36)

  Filter: jsonb_path_exists(data, '$[*]."value"?(@ == $"foo")'::jsonpath, '{"foo": "CEO Staff"}'::jsonb, false)

JIT:

  Functions: 2

  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN