I suggest a MATERIALIZED VIEW
with unnested values and a trigram index as search tools.
If you don't need nested values, hstore
may be even better for you. Use the function svals(hstore)
to unnest hstore
values.
You need to install the additional module hstore
once per database:
CREATE EXTENSION hstore;
Table
CREATE TABLE person AS
SELECT * FROM (
VALUES
(1, 'Joe Doe', hstore 'toy=>car, color=>red')
, (2, 'Jane Doe', 'food=>hamburguer, color=>blue')
) t(person_id, name, preferences);
Materialized view
CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference -- just pref
FROM person p, svals(p.preferences) j(preference);
This is an implicit CROSS JOIN LATERAL
to the set-returning function svals()
.
Trigram Index
You need to install the additional module pg_trgm
once per database:
CREATE EXTENSION pg_trgm;
Then:
CREATE INDEX person_pref_j_trgm_idx ON person_pref_j
USING gin (preference gin_trgm_ops);
Details:
Query
SELECT *
FROM person p
WHERE EXISTS (
SELECT 1
FROM person_pref pp
WHERE pp.person_id = p.person_id
AND pp.preference ILIKE '%burg%'
);
Be aware that this is pretty fast.
If you have nested values or numeric
or boolean
values, jsonb
may be more efficient. You can do the same as above with jsonb_each_text(jsonb)
:
CREATE TABLE person AS
SELECT * FROM (
VALUES
(1, 'Joe Doe', jsonb '{"toy": "car", "color": "red"}')
, (2, 'Jane Doe', '{"food": "hamburguer", "color": "blue"}')
) t(person_id, name, preferences);
CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.key, j.preference -- incl. key or just pref?
FROM person_j p, jsonb_each_text(p.preferences) j(key, preference);
Same index, same query. You might want to add the key to the MV and search for that, too:
CREATE INDEX person_pref_trgm_idx ON person_pref_j
USING GIN (key gin_trgm_ops, preference gin_trgm_ops);