如何在 PostgreSQL 中有效率地尋找大型 JSON 陣列中的元素？

最佳化 PostgreSQL 中的 JSON 陣列元素搜尋

在 PostgreSQL 中儲存的大型 JSON 陣列中有效定位特定元素對於效能至關重要。雖然 PostgreSQL 的 json_array_elements 函數隨時可用，但在處理大量陣列時，它會顯著影響查詢速度。

常見的方法是利用 JSON 陣列上的 GIN 索引。 但是，此方法僅限於包含原始資料類型（數字、字串）的陣列。 JSON 物件陣列不會從這種索引策略中受益。

更強大的解決方案涉及建立自訂函數來提取所需的元素，然後對提取的值進行索引。 即使對於複雜的 JSON 數組結構，這也可以實現高效的查找。

範例實作：

以下是如何建立一個函數來根據鍵提取元素，然後使用 GIN 對其進行索引：

<code class="language-sql">CREATE OR REPLACE FUNCTION extract_element(j JSONB, key TEXT) RETURNS TEXT AS $$
  SELECT value ->> key
  FROM jsonb_each(j)
  WHERE key = key
$$ LANGUAGE SQL IMMUTABLE;

CREATE INDEX tracks_artists_gin_idx ON tracks USING GIN (extract_element(artists, 'name'));</code>

此函數 extract_element 採用 JSONB 物件 (j) 和鍵 (key) 作為輸入。它使用 jsonb_each 迭代 JSONB 對象，並使用 ->> 提取與指定鍵關聯的值。 WHERE 子句確保僅傳回匹配鍵的值。 然後，根據該函數應用於「artists」列的結果建立索引（假設「artists」是一個包含 JSON 物件數組的 JSONB 列，每個物件都有一個「name」鍵）。

改進的查詢效能：

有了這個索引，像下面這樣的查詢將利用索引來顯著加快執行速度：

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

此查詢之前會導致全表掃描，現在可以有效地使用 GIN 索引，大大提高大型資料集的效能。

以上是如何在 PostgreSQL 中有效率地尋找大型 JSON 陣列中的元素？的詳細內容。更多資訊請關注PHP中文網其他相關文章！