如何在 PostgreSQL 中高效搜索 JSON 数组中的元素？

高效搜索PostgreSQL中JSON数组元素的技巧

为了优化对JSON数组中特定元素的查找，务必考虑PostgreSQL的版本和数据结构。

PostgreSQL 9.4 与JSONB

在PostgreSQL 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_path_ops GIN运算符类：

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

查询语句保持不变。

PostgreSQL 9.3 与JSON

对于PostgreSQL 9.3 ，请考虑使用一个IMMUTABLE函数将JSON数组的元素提取为文本值数组：

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

然后，在提取的数组上创建一个函数式GIN索引：

CREATE INDEX tracks_artists_gin_idx ON tracks
USING  gin (json2arr(artists, 'name'));

在查询中，使用数组运算符来匹配索引表达式：

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

请注意，函数索引只能与IMMUTABLE函数一起使用。验证json_array_elements()函数以及使用的任何其他JSON函数的易变性。

以上是如何在 PostgreSQL 中高效搜索 JSON 数组中的元素？的详细内容。更多信息请关注PHP中文网其他相关文章！