短语搜索运算符<使用JSONB文档或仅使用关系表? [英] Does the phrase search operator <-> work with JSONB documents or only relational tables?

问题描述

短语搜索运算符<->是否适用于JSONB文档或仅适用于PostgreSQL中的关系表?

Does the phrase search operator <-> work with JSONB documents or only relational tables in PostgreSQL?

由于尚未设置Postgres托管，我尚未对此进行试验.该问题的答案将有助于确定我将使用的数据库和工具.

I haven't experimented with this yet, as I haven't yet set up Postgres hosting. The answer to this question will help determine what database and what tools I will be using.

我在以下位置找到了此示例代码: https://compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/:

I found this sample code at: https://compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/:

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('jump <-> quick');  

我只需要知道JSONB文档是否支持此运算符即可.

I just need to know if this operator is supported by JSONB documents.

推荐答案

短语搜索功能已集成到文本搜索 数据类型 tsquery .您显示的文本搜索运算符@@ 需要tsvector左边是tsquery.而且tsvector可以从任何字符类型以及JSON文档中构建.

The phrase search capability is integrated into the text search data type tsquery. The text search operator @@ you display takes a tsvector to the left and a tsquery to the right. And a tsvector can be built from any character type as well as from a JSON document.

相关:

• 匹配短语以全文搜索为前缀的结尾

您可以使用json或jsonb文档转换为文本搜索向量-FUNCTIONS-TABLE"rel =" nofollow noreferrer>专用函数:

You can convert your json or jsonb document to a text search vector with one of the dedicated functions:

to_tsvector()
json(b)_to_tsvector()

请注意，这些仅包含JSON文档中的值，而不包括键.通常，这就是您想要的. 基本示例:

Note that these only include values from the JSON document, not keys. Typically, that's what you want. Basic example:

SELECT to_tsvector(jsonb '{"foo":"jump quickly"}')
    @@ to_tsquery('jump <-> quick:*');

在短语搜索上方展示 前缀匹配 .参见:

Demonstrating prefix matching on top of phrase search while being at it. See:

• 从GIN索引的TSVECTOR列中获取部分匹配项

或者，您可以简单地从JSON文档的text表示形式创建tsvector来也包含键名:

Alternatively, you can simply create the tsvector from the text representation of your JSON document to also include key names:

SELECT to_tsvector((jsonb '{"foo-fighter":"jump quickly"}')::text)
    @@ to_tsquery('foo <-> fight:*');

显然会产生更大的tsvector.

两者都可以索引(这是文本搜索的重点).只有索引绑定到关系表. (您可以为表达式建立索引！)
表达式本身可以应用于任何值，而不必像您似乎暗示的那样绑定到表.

Both can be indexed (which is the main point of text search). Only indexes are bound to relational tables. (And you can index the expression!)
The expression itself can be applied to any value, not bound to tables like you seem to imply.

这篇关于短语搜索运算符<使用JSONB文档或仅使用关系表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！