如何在 PostgreSQL 中有效地索引 JSON 陣列以加快元素檢索速度？

高效索引PostgreSQL中的JSON數組以加快元素檢索

在PostgreSQL表中快速辨識和檢索JSON數組內的元素可能具有挑戰性。為了獲得最佳效能，請務必了解適合您特定資料和查詢要求的索引技術。

在PostgreSQL 9.4 中使用JSONB

PostgreSQL 9.4引入了二進位JSON資料型別jsonb，這大大增強了索引可能性。利用jsonb可以直接在JSON數組上建立GIN索引。

<code class="language-sql">CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);</code>

這使得可以使用GIN索引的查詢成為可能，例如：

<code class="language-sql">SELECT * FROM tracks WHERE artists @> '["The Dirty Heads"]';</code>

其中@>表示jsonb的「包含」運算子。

或者，您可以使用jsonb_path_ops GIN運算符類別（非默認，通常更小且更快）：

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists jsonb_path_ops);</code>

針對唯一名稱值進行最佳化

如果您的artists列只包含唯一名稱值的數組，則更有效的方法是將值儲存為JSON文字基元，而不是物件。這消除了冗餘鍵的需要。

<code class="language-sql">CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks VALUES (2, '["The Dirty Heads", "Louis Richards"]');
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);</code>

查詢範例：

<code class="language-sql">SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;</code>

在PostgreSQL 9.3 處理json

對於9.4之前的PostgreSQL版本，可以使用不變函數和GIN索引。

建立函數：

<code class="language-sql">CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';</code>

然後建立函數索引：

<code class="language-sql">CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (json2arr(artists, 'name'));</code>

使用與索引表達式相符的查詢：

<code class="language-sql">SELECT * FROM tracks WHERE '{"The Dirty Heads"}'::text[]</code>

務必確保索引中使用的函數是不變的，以啟用函數索引。關鍵運算子...

文章對原文進行了改寫，使用了更簡潔的語言，並對部分語句進行了調整，但保留了原文的核心內容和圖片位置。

以上是如何在 PostgreSQL 中有效地索引 JSON 陣列以加快元素檢索速度？的詳細內容。更多資訊請關注PHP中文網其他相關文章！