Postgres 9.4+
中的 jsonb
二进制 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
"包含"运算符,可以使用GIN索引。(仅适用于
jsonb
,不适用于
json
!)
或者您可以使用更专业的非默认GIN运算符类
jsonb_path_ops
进行索引:
CREATE INDEX tracks_artists_gin_idx ON tracks
USING gin (artists jsonb_path_ops);
同一个查询。
目前jsonb_path_ops
仅支持@>
运算符。但它通常更小、更快。有更多的索引选项,详见手册。
如果列
artists
仅包含如示例中所示的名称,那么将值仅存储为JSON文本
原语会更有效,并且冗余的
键可以是列名。
请注意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';
?
不能用于对象的值,只能用于键和数组元素。
或者:
CREATE INDEX tracks_artistnames_gin_idx ON tracks
USING gin (artistnames jsonb_path_ops);
查询:
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;
如果名称高度重复,则更有效率。
Postgres 9.3+
中的json
这应该可以使用一个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)';
创建这个
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'));
根据评论中的反馈进行更新。我们需要使用数组运算符来支持GIN索引。
在这种情况下,使用"包含于"运算符<@
。
关于函数不稳定性的注释
即使json_array_elements()
不是不再是,您也可以将函数声明为IMMUTABLE
。
大多数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
函数。