递归嵌套jsonb结构的全文索引

Question

假设我有一本书，它是这样表示的：

{ 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...]
     }
   ]
}

我可以使用postgres’9.4 new jsonb/gin（或其他任何东西）在这个递归嵌套的数据结构的"text"字段上设置全文索引吗？这样，存储在数据库中的图书库就可以按文本搜索（使用索引）？

Answer 1

我刚刚开始研究全文搜索和jsonb类型。一旦您了解了索引在JSONB类型上的工作方式，这似乎是可能的。我发现这个博客系列非常有帮助。

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

此外，Postgres的JSON类型文档包含了一些很好的见解。http://www.postgresql.org/docs/9.4/static/datatype-json.html

Answer 2

一种选择——可能是最好的选择——是创建一个递归函数（函数的官方文档），将整个JSONB结构的text值组合成一个字符串（返回）。您可以对该函数的输出进行全文索引（参见@thomas-wayne-shelton的回答），就好像它只是一个字符串一样。只要在WHERE条件中使用相同的函数，pgSQL就应该识别索引。

我刚刚构建/测试了一个类似的"扁平化"功能，所以我相信它是可以做到的。不幸的是，我的情况大不相同，所以我怀疑代码会混淆而不是启发。我可以说，函数必须标记为IMUTABLE才能支持索引。

===

另一条看起来很有希望的路径（但我认为是死胡同）是递归CTE。

使用递归CTE打开递归数据当然是可能的。以下是官方文档、SO答案和博客示例——最后两个示例专门针对递归嵌套的JSONB。

然而，我不相信你能索引这个输出。我（初步）认为，使用递归CTE的查询很像视图。这是一个运行时优化。我手头不再有参考资料，但我记得最近（截至2018年4月）看到的讨论表明，索引视图还有很长的路要走（我甚至不确定视图索引是否真的适用于CTE）。

Answer 3

我不会回答这个问题；相反，我将提出一种完全不同的方法。

你看过Lucene吗，https://lucene.apache.org/core/?在数据库中实现全文搜索是一个糟糕的设计。您应该使用单独的全文索引器，如Lucene。Lucene文档应该引用数据库密钥，这些密钥可以用来查找数据库中的真实记录。

通过使用Lucene，您很可能会获得比使用数据库的全文搜索功能更好的性能。此外，Lucene比数据库更容易扩展。