我有一张如下所示的表格:
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(artists)
创建 GIN 索引,并使用了 'ARTIST NAME' = ANY names_as_array(artists)
,但是没有使用索引并且查询实际上要慢得多。
最佳答案
jsonb
在 Postgres 9.4+ 中二进制 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"}]';
@>
是 jsonb
"contains" operator ,可以使用 GIN 索引。 (不适用于 json
,仅适用于 jsonb
!)
或者您使用更专业的非默认 GIN 运算符类 jsonb_path_ops
对于索引:
CREATE INDEX tracks_artists_gin_idx ON tracks
USING gin (artists jsonb_path_ops); -- !
相同的查询。
目前jsonb_path_ops
仅支持 @>
运算符(operator)。但它通常更小更快。还有更多索引选项,details in the manual .
如果列artists
仅保存示例中显示的名称,将 values 仅存储为 JSON 文本 primitives 和冗余 会更有效key 可以是列名。
注意 JSON 对象和原始类型的区别:
CREATE TABLE tracks (id serial, <b>artistnames</b> 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';
?
不适用于对象 values,仅适用于 keys 和 array 元素。
或者:
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
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)';
创建 functional index :
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'));
更新了评论中的反馈。我们需要使用 array operators支持 GIN 索引。
"is contained by" operator <@
在这种情况下。
你可以声明你的函数IMMUTABLE
即使json_array_elements()
不是不是。
最多JSON
函数曾经只有 STABLE
,而不是 IMMUTABLE
. There was a discussion on the hackers list to change that.大多数是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
功能。
https://stackoverflow.com/questions/18404055/