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