如何在 Postgres 中高效查询 JSONB 数组的数百万行？

利用 JSONB 运算符和 GIN 索引进行 Postgres 查询

在 Postgres 中，访问 JSON 数组元素需要的语法与您概述的方法不同。要访问 JSON 数组的第一个元素，请使用零索引表达式而不是之前的尝试。

此场景的推荐查询语法是：

e->0->>'event_slug'

此外，Postgres 提供对 jsonb 数据类型的全面支持。使用 Postgres 12 及更高版本，您可以利用 SQL/JSON 路径功能使用 jsonb 列进行“大于”或“小于”比较。

对于您的特定查询，建议的方法是使用以下命令创建 GIN 索引jsonb_path_ops 运算符类：

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

有了此索引，以下查询应该利用该索引并提供高效的结果：

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

在旧版本的 Postgres 中，没有针对 jsonb 的直接“大于”或“小于”运算符，可以采用物化视图来实现类似的性能。这涉及以规范化形式存储相关属性，从而允许您创建传统的 btree 索引以进行高效查询。

考虑到您的用例有数百万行，每行大约包含 10 个事件，GIN 索引和物化视图方法都是可行的解决方案。最佳选择取决于查询的复杂性、更新频率和所需的性能特征等因素。

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