用于在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))

但是，这会进行全表扫描，而且速度不是很快。我尝试使用函数names_as_array（艺术家）创建GIN索引，并使用'ARTIST NAME'=ANYnames_as_array（艺术家），但是没有使用索引并且查询实际上显着变慢。

Answer 1

二进制JSON数据类型jsonb大大改进了索引选项。现在，您可以在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"}]';

<代码>@

或者使用更专业的非默认GIN操作符类jsonb\U path\U ops作为索引：

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

相同的查询。

目前，jsonb\U path\U ops仅支持@

如果列仅保存示例中显示的名称，则只将值存储为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;

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

这应该适用于不可变的函数：

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索引
操作员“is contained by”（被包含）<代码>

即使json\u array\u elements（） <罢工> 不是的 不是。大多数JSON函数过去都是稳定的，而不是不变的。黑客名单上有一个讨论来改变这一点。大多数现在是不可变的。检查：

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

函数索引仅适用于不可变的函数。