Full-text indexing of recursive nested jsonb structures

Question

Let's say I have a book presented like this:

{ title: "Tis no book" chapter: 1, text: "Hello world this is a book chapter", subchapters: [ { chapter: 1.1 text: "Nested sub chapter" subchapters: [ chapter: 1.1.1 text: "Nested nested..." subchapters: [ ...etc...] }, { chapter: 1.2 text: "Nested sub chapter 2" subchapters: [ ...etc...] } ] } 

Can I use 9.4 new jsonb / gin postgres (or something else) to set up a full text index in the "text" field of this recursively nested data structure so that I can search for a library of books stored in the database by text (using index)?

Answer 1

I just started to study full-text search and jsonb types. It seems like this is possible if you understand how indexes work on JSONB types. I found this blog series very useful.

https://bibhas.in/blog/postgresql-swag-part-2-indexing-json-data-type-and-full-text-search/

In addition, the Postgres JSON documentation contains some useful information. http://www.postgresql.org/docs/9.4/static/datatype-json.html

Answer 2

I will not answer the question; instead, I am going to propose a completely different approach.

Have you looked at Lucene, https://lucene.apache.org/core/ ? Implementing full-text database searches is poor design. You should use a separate full-text index such as Lucene. Lucene documents must reference database keys that can be used to find the actual record in the database.

Using Lucene, you are likely to get much better performance than using full-text database search functions. In addition, Lucene is much easier to scale than your database.