如何在 Postgres 中高效查詢 JSONB 數組的數百萬行？

利用 JSONB 運算子和 GIN 索引進行 Postgres 查詢

在 Postgres 中，存取 JSON 陣列元素所需的語法與您概述的方法不同。若要存取 JSON 陣列的第一個元素，請使用零索引表達式而不是先前的嘗試。

此場景的建議查詢語法是：

e->0->>'event_slug'

此外，Postgres 提供對 jsonb 資料類型的全面支援。使用 Postgres 12 及更高版本，您可以利用 SQL/JSON 路徑功能使用 jsonb 列進行「大於」或「小於」比較。

對於您的特定查詢，建議的方法是使用以下命令建立GIN 索引jsonb_path_ops 運算子類別：

CREATE INDEX locations_events_gin_idx ON locations USING GIN (events jsonb_path_ops);

有了此索引，以下查詢應該利用該索引並提供高效的結果：

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1") ? (@.end_time.datetime() < "2014-10-13".datetime())'

在舊版的Postgres 中，沒有針對jsonb的直接“大於”或“小於”運算符，可以採用物化視圖來實現類似的效能。這涉及以規範化形式儲存相關屬性，從而允許您建立傳統的 btree 索引以進行高效查詢。

考慮到您的用例有數百萬行，每行大約包含 10 個事件，GIN 索引和物化視圖方法都是可行的解決方案。最佳選擇取決於查詢的複雜度、更新頻率和所需的效能特性等因素。

以上是如何在 Postgres 中高效查詢 JSONB 數組的數百萬行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！