jsonb 可以通過 value 甚至部分值進行快速搜尋嗎？其他類型？

我有一個person必須插入任意數據的表。由於我使用的是 Postgres 9.4，jsonb因此似乎是正確的選擇。

範例數據：

id: 1, name: "Joe Doe", preferences: { color: "red" , toy: "car"}
id: 2, name: "Jane Doe", preferences: { color: "blue", food: "hamburguer" }

問題是我需要通過變數值來查詢它，例如：

所有偏好有“漢堡”的人，部分查詢肯定也是可以的，所以搜尋“漢堡”一定會給我帶來記錄2。

使用json函式，我可以搜尋值，只要我知道key，對吧？還是有一種快速搜尋值的方法？

我想做的是創建一個 tsvector 列並轉儲 json 以使用全文搜尋來執行部分查詢。

對於Postgres 12或更高版本，請考慮 SQL/JSON 路徑語言。看：

• JSON 值的模式匹配：物化視圖上的慢 EXISTS 子查詢

---

原始答案（對於舊版本）

我建議MATERIALIZED VIEW使用未嵌套的值和三元索引作為搜尋工具。

hstore

如果您不需要嵌套值，hstore則可能對您更好。使用該函式svals(hstore)取消嵌套hstore值。

您需要為hstore每個數據庫安裝一次附加模組：

CREATE EXTENSION hstore;

• PostgreSQL 中的鍵值對

桌子

CREATE TABLE person (
 person_id int PRIMARY KEY
, name text
, preferences jsonb
);
INSERT INTO person VALUES
 (1, 'Joe Doe', jsonb  '{"toy": "car", "color": "red"}')
, (2, 'Jane Doe', '{"food": "hamburguer", "color": "blue"}')
;

物化視圖

CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference        -- just pref
FROM   person p, svals(p.preferences) j(preference);

CROSS JOIN LATERAL這是對 set-returning 函式的隱含svals()。

三元索引

您需要為pg_trgm每個數據庫安裝一次附加模組：

CREATE EXTENSION pg_trgm;

然後：

CREATE INDEX person_pref_trgm_idx ON person_pref
USING gin (preference gin_trgm_ops);

細節：

• LIKE 是如何實現的？
• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配

詢問

SELECT *
FROM   person p
WHERE  EXISTS (
  SELECT FROM person_pref pp
  WHERE  pp.person_id = p.person_id
  AND    pp.preference ILIKE '%burg%'
  );

這對於選擇性搜尋詞來說很快。

jsonb

如果您有嵌套的值或numeric或boolean值，jsonb可能會更有效。您可以使用以下方法執行與上述相同的操作jsonb_each_text(jsonb)：

CREATE MATERIALIZED VIEW person_pref AS
SELECT p.person_id, j.preference
FROM  person p, jsonb_each_text(p.preferences) AS j(key, preference);

相同的索引，相同的查詢。

db<>在這裡擺弄

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