如何在 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中文网其他相关文章！