我有一個儲存兩個人之間對話的表。

數據將如下所示：

CREATE TABLE foo
AS
 SELECT $$[
   { "user": 1, "timestamp": 1, "message": "First message" },
   { "user": 2, "timestamp": 2, "message": "Second message" },
   { "user": 2, "timestamp": 3, "message": "Debounced message from same user" },
   { "user": 1, "timestamp": 4, "message": "Last message" }
 ]$$::jsonb AS jsondata;

我從不需要單獨查找每條消息，所以我只想將整個對話儲存在一個jsonb欄位中。我需要對所有消息執行全文搜尋。

我的第一個想法是創建一個新的文本列，將所有消息連接成一個長字元串，並在該列上創建一個三元組 GIN 索引。

這似乎是一種浪費大量空間的技巧，所以我想避免中間列。如何直接從jsonb列創建索引？

我讀這個問題的方式，你只關心message. 這裡的困難是你需要，

1. 映射到返回消息元素的 json 數組
2. 將消息元素字元串數組減少/折疊為聚合字元串。

這在函式式程式中很容易。使用 PostgreSQL 中的常用函式並不容易，並且很難使其與聲明性語言一起使用。也許有一天你會有一個jsonb_array_elements(jsonb [,path])可以幫助你的方法，但在那之前我們可以在我們的數據庫中創建一個函式。

使用 plpgsql 創建函式

請注意，這可能不像 plv8 函式那樣快速或乾淨，但在下一個版本中，我們將返回一個tsvector.

這裡我們使用jsonb_array_elements擴展 json，然後將'message'元素聚合成一個字元串。

CREATE OR REPLACE FUNCTION jsonb_message_to_string( jsondata jsonb, out string text )
AS $func$
 BEGIN
   SELECT INTO string
     string_agg(d->>'message', ' ')
   FROM jsonb_array_elements(jsondata) AS d;
   RETURN;
 END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

創建tsvector_agg和改進我們的功能。

這個函式還不是最優的，因為它返回一個字元串。但是，從 9.6 開始，PostgreSQL 還沒有提供第二個困難tsvector_agg。但是，它是 PostgreSQL，所以我們可以製作一個..

CREATE AGGREGATE tsvector_agg (tsvector) (
 SFUNC = tsvector_concat,
 STYPE = tsvector
);

這允許我們現在返回一個聚合 tsvector，它更快並保留位置資訊。現在我們可以改進我們的功能。在這裡，我們創建一個新的jsonb_message_to_tsvector.

CREATE OR REPLACE FUNCTION jsonb_message_to_tsvector( jsondata jsonb, out tsv tsvector )
AS $func$
 BEGIN
   SELECT INTO tsv
     tsvector_agg(to_tsvector(d->>'message'))
   FROM jsonb_array_elements(jsondata) AS d;
   RETURN;
 END;
$func$ LANGUAGE plpgsql
IMMUTABLE;

現在我們可以創建我們的索引..

CREATE INDEX ON FOO
 USING gin (jsonb_message_to_tsvector(jsondata));

我們會像這樣查詢它..

SELECT jsonb_message_to_tsvector(jsondata) @@ 'first'
FROM foo;

引用自：https://dba.stackexchange.com/questions/170901