如何在 PostgreSQL 中高效索引和查詢 JSONB 陣列？

在 Postgres JSONB 中查詢數組結構的正確索引

要有效地索引和查詢儲存在 Postgres JSONB欄位中的結構化資料數組，考慮以下步驟：

1.正確的陣列存取：

使用正確的語法存取 JSONB 上下文中的陣列元素。例如：

e->0->>'event_slug'

2。 JSONB 的自訂運算子：

利用 jsonpath 運算子對 JSONB 欄位進行大於或小於比較。在 Postgres 12 或更高版本中，使用 @?:

e->0->>'end_time' @> '2014-10-13'

或者，在舊版本中，使用 jsonb_path_ops:

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

3。 Postgres 12 的基本解決方案：

對於相等性檢查，請使用@?運算子：

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1")';

對於OR 類型過濾器，請使用以下語法：

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

4.適用於任何Postgres版本的基本解決方案：

對於相等性檢查，請使用@>運算子：

SELECT * FROM locations WHERE events @> '[{"event_slug":"test_1"}]';

對於大於或等於檢查，請使用子查詢：

SELECT l.*
FROM locations l
JOIN jsonb_array_elements(l.events) e ON l.events @> '[{"event_slug":"test_1"}]'
WHERE (e->>'end_time')::timestamp >= '2014-10-30 14:04:06'::timestamptz;

5。使用物化視圖的高級解決方案：

為了在複雜查詢上獲得最佳效能，請考慮建立一個以規範化形式儲存相關資料的物化視圖：

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

6.索引並查詢物化視圖：

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

以上是如何在 PostgreSQL 中高效索引和查詢 JSONB 陣列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！