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

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

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 tracksUSING  gin (artists **jsonb_path_ops** );

相同的查询。

当前

jsonb_path_ops

仅支持

@>

运营商。但是它通常更小，更快。手册中还有更多索引选项和
详细信息
。

---

如果

artists

仅保留示例中显示的名称，则存储一个冗余度较小的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 tracksUSING  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 tracksUSING  gin (json2arr(artists, 'name'));

并使用这样的 查询 。

WHERe

子句中的表达式必须与索引中的表达式匹配：

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

更新了评论反馈。我们需要使用
数组运算符 来支持GIN索引。
该“包含由”运营商

<@

在这种情况下。

功能波动注意事项

IMMUTABLE

即使

json_array_elements()

不是， 也可以声明函数。
大多数

JSON

功能过去只是

STABLE

，而没有

IMMUTABLE

。黑客名单上进行了讨论，以改变这一点。

IMMUTABLE

现在大多数。检查：

SELECt p.proname, p.provolatileFROM   pg_proc pJOIN   pg_namespace n ON n.oid = p.pronamespaceWHERe  n.nspname = 'pg_catalog'AND    p.proname ~~* '%json%';

功能索引仅与

IMMUTABLE

功能一起使用。