PostgreSQL 14中使用JSONPath比较日期字符串的问题

你原有的JSONPath查询语句存在语法结构错误，核心是误用了数组过滤操作符?。官方示例里的?用于过滤数组元素（比如$[*] ? (...)是遍历数组逐个筛选），但你的first_segment_exited_at是单个字符串属性，不需要用?包裹条件。

正确的JSONPath查询语句 #

SELECT id, profile
FROM end_user
WHERE profile @? '$.first_segment_exited_at.datetime() < "2023-05-25".datetime()'::jsonpath;

逻辑说明 #

• 直接对$.first_segment_exited_at这个字符串属性调用.datetime()，将其转换为PostgreSQL日期时间类型
• 与目标日期字符串"2023-05-25"调用.datetime()后的结果做比较，逻辑和你原有普通查询完全一致，且能适配你提供的ISO格式时间字符串

索引优化（实现你的性能需求） #

若要让该查询利用索引，需创建jsonb_path_ops类型的GIN索引：

CREATE INDEX idx_end_user_profile_path ON end_user USING gin (profile jsonb_path_ops);

该索引专门针对JSONPath查询做优化，可让@?操作符高效检索数据。

内容的提问来源于stack exchange，提问作者Tim