Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- base table
CREATE TABLE person (
  person_id int PRIMARY KEY
, name text
, preferences jsonb
);
INSERT INTO person VALUES
  (1, 'Joe Doe', jsonb  '{"toy": "car", "color": "red"}')
, (2, 'Jane Doe', '{"food": "hamburguer", "color": "blue"}')
;

-- materialized view
CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference   -- , j.key -- incl. key or just preference?
FROM  person p, jsonb_each_text(p.preferences) j(key, preference);

-- idx
CREATE INDEX person_pref_trgm_idx ON person_pref
USING GIN (preference gin_trgm_ops);

CREATE TABLE

INSERT 0 2

SELECT 4

CREATE INDEX

SELECT *
FROM   person p
WHERE  EXISTS (
   SELECT FROM person_pref pp
   WHERE  pp.person_id = p.person_id
   AND    pp.preference ILIKE '%burg%'
   );

person_id
name
preferences

2
Jane Doe
{"food": "hamburguer", "color": "blue"}

SELECT 1

SET enable_seqscan = off;  -- to show possible query plan for big tables

EXPLAIN ANALYZE
SELECT *
FROM   person p
WHERE  EXISTS (
   SELECT FROM person_pref pp
   WHERE  pp.person_id = p.person_id
   AND    pp.preference ILIKE '%burg%'
   );

SET

QUERY PLAN

Nested Loop  (cost=16.17..24.21 rows=1 width=68) (actual time=0.703..0.704 rows=1 loops=1)

  ->  HashAggregate  (cost=16.02..16.03 rows=1 width=4) (actual time=0.695..0.695 rows=1 loops=1)

        Group Key: pp.person_id

        Batches: 1  Memory Usage: 24kB

        ->  Bitmap Heap Scan on person_pref pp  (cost=12.00..16.01 rows=1 width=4) (actual time=0.691..0.692 rows=1 loops=1)

              Recheck Cond: (preference ~~* '%burg%'::text)

              Heap Blocks: exact=1

              ->  Bitmap Index Scan on person_pref_trgm_idx  (cost=0.00..12.00 rows=1 width=0) (actual time=0.544..0.545 rows=1 loops=1)

                    Index Cond: (preference ~~* '%burg%'::text)

  ->  Index Scan using person_pkey on person p  (cost=0.15..8.17 rows=1 width=68) (actual time=0.006..0.006 rows=1 loops=1)

        Index Cond: (person_id = pp.person_id)

Planning Time: 0.185 ms

Execution Time: 1.152 ms

EXPLAIN