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

Question

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

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

但是，这会进行全表扫描，而且速度不是很快。我尝试使用function创建一个GIN索引names_as_array(artists)，并使用'ARTIST NAME' = ANY names_as_array(artists)，但是未使用该索引，查询实际上要慢得多。

Answer 1

jsonb 在Postgres 9.4+

使用新的二进制JSON数据类型
jsonb
，Postgres 9.4引入了 大大改进的索引选项 。现在，您可以jsonb直接在数组上具有GIN索引：

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，仅适用于jsonb！）

或者， 您可以使用更专门的非默认GIN运算符类
jsonb_path_ops
作为索引：

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

相同的查询。

当前jsonb_path_ops仅支持@>运营商。但是它通常更小，更快。手册中还有更多索引选项和
详细信息
。

如果 artists所显示的例子仅持有的名字，这将是更有效地存储较小冗余JSON值开始：刚刚 值 作为文本 基元 和冗余
键 可以在列名。

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

询问：

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

? 不适用于对象 值 ，仅适用于 键 和 数组元素 。
或（如果经常重复使用名称，效率会更高）：

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 功能 一起
工作 ：

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子句中的表达式必须与索引中的表达式匹配：

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

已更新，并在评论中提供了反馈。我们需要使用
数组运算符 来支持GIN索引。
该“包含由”运营商 <@在这种情况下。

有关功能波动性的注意事项

IMMUTABLE即使json_array_elements() 不是， 也可以声明函数。
大多数JSON功能过去只是STABLE，而没有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功能一起使用。