0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.