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

高效搜尋PostgreSQL中JSON陣列元素的技巧

為了優化JSON數組中特定元素的查找，請務必考慮PostgreSQL的版本和資料結構。

PostgreSQL 9.4 與JSONB

在PostgreSQL 9.4及更高版本中，二進位JSON資料型別jsonb提供了顯著改進的索引功能。您可以直接在jsonb陣列上建立GIN索引：

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

此索引允許使用@>運算子進行高效查詢：

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

或者，您可以使用專門的jsonb_path_ops GIN運算符類別：

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

查詢語句保持不變。

PostgreSQL 9.3 與JSON

對於PostgreSQL 9.3 ，請考慮使用一個IMMUTABLE函數將JSON數組的元素提取為文字值數組：

<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>

然後，在擷取的陣列上建立一個函數式GIN索引：

<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[] <@ (json2arr(artists, 'name'));</code>

請注意，函數索引只能與IMMUTABLE函數一起使用。驗證json_array_elements()函數以及使用的任何其他JSON函數的易變性。

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