如何在 PostgreSQL 中高效索引和查询 JSONB 数组？

在 Postgres JSONB 中查询数组结构的正确索引

要有效地索引和查询存储在 Postgres JSONB 字段中的结构化数据数组，考虑以下步骤：

1.正确的数组访问：

使用正确的语法访问 JSONB 上下文中的数组元素。例如：

e->0->>'event_slug'

2。 JSONB 的自定义运算符：

利用 jsonpath 运算符对 JSONB 字段进行大于或小于比较。在 Postgres 12 或更高版本中，使用 @?:

e->0->>'end_time' @> '2014-10-13'

或者，在旧版本中，使用 jsonb_path_ops:

CREATE INDEX events_gin_idx ON locations USING GIN (events jsonb_path_ops);

3。 Postgres 12 的基本解决方案：

对于相等性检查，请使用 @?运算符：

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1")';

对于 OR 类型过滤器，请使用以下语法：

SELECT l.*
FROM locations l
WHERE l.events @? '$[*] ? (@.event_slug == "test_1") ? (@.start_time.datetime() < "2014-10-13".datetime() || @.end_time.datetime() < "2014-10-13".datetime())';

4.适用于任何 Postgres 版本的基本解决方案：

对于相等性检查，请使用 @>运算符：

SELECT * FROM locations WHERE events @> '[{"event_slug":"test_1"}]';

对于大于或等于检查，请使用子查询：

SELECT l.*
FROM locations l
JOIN jsonb_array_elements(l.events) e ON l.events @> '[{"event_slug":"test_1"}]'
WHERE (e->>'end_time')::timestamp >= '2014-10-30 14:04:06'::timestamptz;

5。使用物化视图的高级解决方案：

为了在复杂查询上获得最佳性能，请考虑创建一个以规范化形式存储相关数据的物化视图：

CREATE MATERIALIZED VIEW loc_event AS
SELECT l.location_id, e.event_slug, e.end_time
FROM locations l, jsonb_populate_recordset(null::event_type, l.events) e;

6.索引并查询物化视图：

CREATE INDEX loc_event_idx ON loc_event (event_slug, end_time, location_id);

SELECT *
FROM loc_event
WHERE event_slug = 'test_1'
AND end_time >= '2014-10-30 14:04:06 -0400'::timestamptz;

以上是如何在 PostgreSQL 中高效索引和查询 JSONB 数组？的详细内容。更多信息请关注PHP中文网其他相关文章！