用于在 JSON 数组中查找元素的索引

我有一个看起来像这样的表：

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 是有原因的。

我想做的是查找具有特定的曲目艺术家姓名（完全符合）。

我正在使用这个查询：

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

然而，这会进行全表扫描，而且速度不是很快。我尝试使用函数创建 GIN 索引names_as_array(artists)，并使用'ARTIST NAME' = ANY names_as_array(artists)，但是没有使用索引，并且查询实际上明显慢了。

jsonb在 Postgres 9.4+ 中

二进制 JSON 数据类型jsonb很大程度上改进了索引选项。您现在可以在以下位置拥有 GIN 索引：jsonb直接数组：

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

不需要函数来转换数组。这将支持查询：

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

@>是jsonb“包含”运算符，可以使用GIN索引。 （不是为了json, only jsonb!)

Or您使用更专业的非默认 GIN 操作符类jsonb_path_ops对于索引：

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

同样的查询。

现在jsonb_path_ops只支持@>操作员。但它通常更小、更快。还有更多索引选项，手册中的详细信息.

If专栏artists仅保存示例中显示的名称，仅存储名称会更有效values作为 JSON 文本原语和多余的key可以是列名称。

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

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

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

Query:

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

?不适用于对象values, just keys and 数组元素.

Or:

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

Query:

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

如果名称高度重复，效率会更高。

json在 Postgres 9.3+ 中

这应该与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)';

创建这个功能性的index:

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

并使用一个query像这样。中的表达式为WHERE子句必须与索引中的子句匹配：

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

根据评论中的反馈进行了更新。我们需要使用数组运算符支持GIN索引。
The “包含于”运算符<@在这种情况下。

关于函数波动性的注释

你可以声明你的函数IMMUTABLE即使json_array_elements()不是 不是。
Most JSON以前的功能只是STABLE, not IMMUTABLE. 黑客名单上有一场讨论要改变这一点。大多数是IMMUTABLE现在。检查：

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功能。