Query Quill-Delta JSON array in Postgres I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g.

-----------------------------------------------------------------
| id | title | content                                          |
-----------------------------------------------------------------
| 10 | Hello | [ { "insert": "Hello" }, { "insert": "World" } ] |
| 20 | Image | [ { "insert": { "image": "data:image/j..." } } ] |
-----------------------------------------------------------------


Then, I need to query it, e.g. select where text contains "hello world" such as:


if I query content for "hello world" I get row 10;
if I query content for "insert" I get no results;
if I query content form "image" I get no results.


In practice, it should search only the insert key of array objects, where the value is a JSON string (no other types).

Can I achieve this storing data in Postgres? I read about full-text search over JSONB, but I couldn't come up with a working example.
I've come up with a function and an index; this is the function, which takes alle the insert JSON-String keys and builds up a tsvector from them:

create or replace function public.fts_quill_delta(lang regconfig, delta text) returns tsvector as
$$
begin
return to_tsvector(lang, string_agg(value-&gt;&gt;'insert', ' ')) from jsonb_array_elements(delta::jsonb) where jsonb_typeof(value-&gt;'insert') = 'string';
end;
$$
language 'plpgsql' immutable;


this is the index:

create index content_delta_fts on my_table using gin(fts_quill_delta(lang, content));


Then you can query the index with:

select from my_table where fts_quill_delta(lang, content) @@ 'hello &amp; world'


Note that I added a lang column, used by the FTS engine to use correct I18N settings.