有效搜尋整個 1 級嵌套 JSONB

假設我們需要檢查 jsonb 列是否包含與任何值中的子字元串匹配的特定值（非嵌套，僅第一級）。

如何有效地優化查詢以在整個JSONB列中搜尋每個值？

ILIKE %val%對轉換為文本的 jsonb 數據類型有什麼好的替代方法嗎？

jsonb_each_text(jsonb_column) ILIKE '%val%'

例如，考慮以下數據：

SELECT '{"col1": "somevalue", "col2": 5.5, "col3": 2016-01-01, "col4": "othervalue", "col5": "yet_another_value"}'::JSONB

%val%當需要在包含不同鍵配置的記錄中搜尋模式時，您將如何優化這樣的查詢？將每個鍵值對提取為文本並執行 ILIKE/POSIX 搜尋是否有更好的選擇？

主要是，我正在尋找一種不同的替代方法，將整個 jsonb 欄位解壓縮到單獨的鍵行中，並將它們的值作為 text。

對於模式匹配，目前（第 10 頁）使用標準運算符和索引沒有比取消嵌套值並單獨測試每個值更好的方法。至少，使用有效的EXISTS查詢：

SELECT *
FROM   tbl
WHERE  EXISTS (SELECT FROM jsonb_each_text(jsonb_column) WHERE value ~* 'val');

針對此特定案例的專門解決方案：將每行的所有字元串值與一個IMMUTABLE函式連接起來，並在表達式上創建一個三元組 GIN 索引。您需要安裝附加模組pg_trgm。如果您不熟悉，請先閱讀以下內容：

• LIKE 是如何實現的？

功能：

CREATE OR REPLACE FUNCTION f_all_value_string(jsonb)
 RETURNS text AS
$func$
SELECT string_agg(value, '|') FROM jsonb_each_text($1)
$func$  LANGUAGE sql IMMUTABLE;

選擇一個不會干擾您的模式的分隔符 - ‘|’ 在範例中。

指數：

CREATE INDEX tbl_all_value_string_trigram_idx ON tbl
USING GIN (f_all_value_string(jsonb_column) gin_trgm_ops); 

查詢（匹配索引）：

SELECT * FROM tbl WHERE f_all_value_string(jsonb_column) ~* 'val';

有關的：

• 使用更大的運算符在 jsonb 數組中搜尋嵌套值
• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配

對於大量數據，也許您可以通過添加全文 GIN 索引來降低全文搜尋的成本，無論是針對整個 json ( to_tsvector(jsonb_column::text)) 還是提取並連接其所有相關值的函式的輸出。

然後，您可以使用@@ ts_query()運算符來獲得可能匹配的縮減子集，並使用ILIKEor在該子集中搜尋~。

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