用于在JSON数组中查找元素的索引 [英] Index for finding an element in a JSON array

问题描述

我有一个如下所示的表：

I have a table that looks like this:

CREATE TABLE tracks (id SERIAL, artists JSON);

INSERT INTO tracks (id, artists) 
  VALUES (1, '[{"name": "blink-182"}]');

INSERT INTO tracks (id, artists) 
  VALUES (2, '[{"name": "The Dirty Heads"}, {"name": "Louis Richards"}]');

还有其他几个与此问题无关的列。将它们存储为JSON是有原因的。

There's several other columns that aren't relevant to this question. There's a reason to have them stored as JSON.

我要做的是查找具有特定艺术家名称（完全匹配）的曲目。

What I'm trying to do is lookup a track that has a specific artist name (exact match).

我正在使用此查询：

SELECT * FROM tracks 
  WHERE 'ARTIST NAME' IN
    (SELECT value->>'name' FROM json_array_elements(artists))

例如

SELECT * FROM tracks
  WHERE 'The Dirty Heads' IN 
    (SELECT value->>'name' FROM json_array_elements(artists))

但是，这样做了全表扫描，并不是很快。我尝试使用函数 names_as_array（艺术家）创建GIN索引，并使用'ARTIST NAME'= ANY names_as_array（艺术家），但是没有使用索引，查询实际上要慢得多。

However, this does a full table scan, and it isn't very fast. I tried creating a GIN index using a function names_as_array(artists), and used 'ARTIST NAME' = ANY names_as_array(artists), however the index isn't used and the query is actually significantly slower.

推荐答案

jsonb Postgres中的 9.4 +

使用新的二进制JSON数据类型 jsonb ，Postgres 9.4引入大幅提升索引选项。您现在可以直接在 jsonb 数组中拥有GIN索引：

jsonb in Postgres 9.4+

With the new binary JSON data type jsonb, Postgres 9.4 introduced largely improved index options. You can now have a GIN index on a jsonb array directly:

CREATE TABLE tracks (id serial, artists jsonb);
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);

无需转换数组的函数。这将支持查询：

No need for a function to convert the array. This would support a query:

SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';

@> 成为新的 jsonb 包含运算符，可以使用GIN索引。 （不适用于 json 类型，只有 jsonb ！）

@> being the new jsonb "contains" operator, which can use the GIN index. (Not for type json, only jsonb!)

或您使用更专业的非默认GIN运算符类 jsonb_path_ops ：

Or you use the more specialized, non-default GIN operator class jsonb_path_ops for the index:

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (artists jsonb_path_ops);

相同查询。

目前 jsonb_path_ops 仅支持 @ > 运算符。但它通常更小更快。还有更多索引选项， 手册中的详细信息 。

Currently jsonb_path_ops only supports the @> operator. But it's typically much smaller and faster. There are more index options, details in the manual.

如果 艺术家只保存示例中显示的名称，以更低效的方式存储较少的冗余JSON值：只需将值作为文本 原语 和冗余的键可以在列名中。

If artists only holds names as displayed in the example, it would be more efficient to store a less redundant JSON value to begin with: just the values as text primitives and the redundant key can be in the column name.

注意JSON对象和原语之间的区别类型：

Note the difference between JSON objects and primitive types:

• 在PostgreSQL中使用json数组中的索引

• Using indexes in json array in PostgreSQL

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

查询：

SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';

？ 对于对象不起作用值，只是键和数组元素。

或者（如果经常重复名称，效率更高）：

? does not work for object values, just keys and array elements.
Or (more efficient if names are repeated often):

CREATE INDEX tracks_artistnames_gin_idx ON tracks
USING  gin (artistnames jsonb_path_ops);

查询：

SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;

json 在Postgres 9.3 +

这应该适用于 IMMUTABLE 功能 ：

json in Postgres 9.3+

This should work with an IMMUTABLE function:

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)';

创建此功能索引 ：

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));

并使用像这样的查询。 WHERE 子句中的表达式必须与索引中的表达式匹配：

And use a query like this. The expression in the WHERE clause has to match the one in the index:

SELECT * FROM tracks
WHERE  '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));

更新了评论中的反馈。我们需要使用 阵列运营商 支持GIN索引。

< @ 包含noreferrer>。

Updated with feedback in comments. We need to use array operators to support the GIN index.
The "is contained by" operator <@ in this case.

即使 IMMUTABLE > json_array_elements（） 不是不是。

大多数 JSON 以前的函数只有 STABLE ，而不是 IMMUTABLE 。 有关于黑客的讨论列表来改变它。现在大多数是 IMMUTABLE 。检查：

You can declare your function IMMUTABLE even if json_array_elements() isn't wasn't.
Most JSON functions used to be only STABLE, not IMMUTABLE. There was a discussion on the hackers list to change that. Most are IMMUTABLE now. Check with:

SELECT p.proname, p.provolatile
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'pg_catalog'
AND    p.proname ~~* '%json%';

功能索引仅适用于 IMMUTABLE 函数。

Functional indexes only work with IMMUTABLE functions.

这篇关于用于在JSON数组中查找元素的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！