Query Quill-Delta JSON array in Postgres

Question

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:

1. if I query content for "hello world" I get row 10;
2. if I query content for "insert" I get no results;
3. 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.

Answer 1

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->>'insert', ' ')) from jsonb_array_elements(delta::jsonb) where jsonb_typeof(value->'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 & world'

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