-1

Given the following dataset:

-- Create random table
CREATE TABLE sample (
    id serial primary key,
    content jsonb
);

-- Insert sample rows
INSERT INTO sample (content)
VALUES 
    ('{"text": ["Lorem ipsum dolor sit amet","consectetur adipiscing elit","sed do eiusmod tempor incididunt","ut labore et dolore magna aliqua"]}'), 
    ('{"text": ["Ut enim ad minim veniam","quis nostrud exercitation ullamco laboris","nisi ut aliquip ex ea commodo consequat","Duis aute irure dolor in reprehenderit","voluptate velit esse cillum dolore"]}'), 
    ('{"text": ["eu fugiat nulla pariatur","Excepteur sint occaecat cupidatat","non proident, sunt in culpa qui","officia deserunt mollit anim id est laborum"]}')
;

content is a JSONB column that contains a key text which is a Json array of texts.

I would like to be able to search with ILIKE or a similar function within this text using an index.

Functionally this works and this is what I would like to do:

WITH ctr AS (
    SELECT id, jsonb_array_elements_text((content->>'text')::jsonb) as mytext
    FROM sample
)
SELECT id
FROM ctr
WHERE mytext ILIKE '%qui%';

 id 
----
  2
  2
  3
(3 rows)

Is there a solution to have the same output using an index? I am looking to query a very large dataset.

It looks like it's not possible to use a trigram index (ERROR: operator class "gist_trgm_ops" does not accept data type jsonb)

Gab
  • 3,404
  • 1
  • 11
  • 22

1 Answers1

0

You can use regular expressions

create index idx_name on sample ((content->>'text'));

select sample.*
from sample
where content->>'text' ~ ' qui';

select sample.* 
from sample, jsonb_array_elements_text(content->'text') many(elem)
where elem ~ ' qui';

Besides, from Postgresql version 12+ you can use the "like_regex" to search in your JSONB column.

https://www.postgresql.org/docs/current/functions-json.html

Check out Selecting for a Jsonb array contains regex match and How to create index on json field in Postgres 9.3

Ozgur G
  • 26
  • 4