Postgresql – Query Quill-Delta JSON array in Postgres

full-text-searchjsonpostgresql

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.

Best Answer

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.

Related Solutions

PostgreSQL JSON field performance with large documents

In PostgreSQL 9.3, performance will be fairly poor for large json documents. The whole document must be parsed in order to access a field, index an array, etc. It's stored on disk as a simple text representation, the json text.

PostgreSQL 9.4 is likely to change this, with support for jsonb storage on disk. It'll still have to read the whole json object from disk to access any part of it, but it won't have the same kinds of parsing overheads you'll see in 9.3. It'll also gain many of the indexing features of hstore.

In 9.3, I strongly suspect that hstore, which is stored in a compact binary representation on disk and supports a variety of indexing features, is much more appropriate for your needs.

Postgresql – CRUD operations on json data

Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb is your data type.

9.3 json https://gist.github.com/matheusoliveira/9488951

(and while jsonb is not an option for Michael, for those who are using 9.4, there is a jsonb variant: https://gist.github.com/inindev/2219dff96851928c2282)

Operations Provided:

postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
        json_append        
---------------------------
 {"a":1,"b":2,"c":3,"a":4}
(1 row)

postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
 json_delete 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
 json_update 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
     json_merge      
---------------------
 {"b":2,"c":3,"a":4}
(1 row)

Best Answer

Related Solutions

PostgreSQL JSON field performance with large documents

Postgresql – CRUD operations on json data

Related Question