I have a table with a jsonb column where I store variable data. I would like to search this column and also find fragments (leading or trailing whitespace). I think I know how to do this with text columns but cannot wrap my head around how to achieve this with jsonb
columns.
There are two scenarios that I would like to achieve:
- Search a specific key inside the jsonb column only (for example
data->>company
) - Search the whole
jsonb
column
For text columns I generate gin indexes using pg_trgm.
Install extension pg_trgm:
CREATE extension if not exists pg_trgm;
Create table & index:
CREATE TABLE tbl (
col_text text,
col_json jsonb
);
CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_text gin_trgm_ops);
Example query:
SELECT * FROM tbl WHERE col_text LIKE '%foo%'; -- leading wildcard
SELECT * FROM tbl WHERE col_text ILIKE '%foo%'; -- works case insensitive as well
Trying the same with the jsonb column fails. If I try to index the whole column
CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_json gin_trgm_ops);
I get the error
ERROR (datatype_mismatch): operator class "gin_trgm_ops" does not accept data type jsonb
(Which makes sense). If I try to index just one key of the jsonb column I also receive an error:
CREATE INDEX table_col_trgm_idx ON tbl USING gin (col_json->>company gin_trgm_ops);
Error:
ERROR (syntax_error): syntax error at or near "->>"
I used this answer by @erwin-brandstetter as a reference. Any help is highly appreciated (and no, I don't want to implement Elasticsearch as of now :) ).
Edit: Creating the index like this actually works:
CREATE INDEX table_col_trgm_idx ON tbl USING gin ((col_json->>'company') gin_trgm_ops);
And querying it also doesn't lead to an error:
SELECT * FROM tbl WHERE col_json->>'company' LIKE '%foo%';
But the result is always empty.