如何在PostgreSQL 13的JSONPath中比较时间间隔（时长）并筛选符合条件的JSON对象

我来帮你搞定这个JSONPath筛选的需求，先梳理下核心问题和修正方案：

核心问题分析 #

你的输入JSON里有个小插曲：第二个对象的结束时间键是ende（疑似笔误），而第一个是end，原SQL只引用@.end会漏掉第二个对象的时间值，另外还要确保时间间隔的计算逻辑在JSONPath里是正确的。

修正后的SQL语句 #

如果想返回符合条件的整个对象，可以用下面的语句：

SELECT jsonb_path_query_array_tz(
  '[ { "name" : "oneDay", "start": "2021-07-02 00:00:00", "end": "2021-07-03 00:00:00" }, { "name" : "threeDays", "start": "2021-07-15 00:00:00", "ende": "2021-07-18 00:00:00" } ]'::jsonb,
  '$[*] ? ( coalesce(@.end, @.ende).datetime() - @.start.datetime() > "1 day" )'
);

如果只需要返回符合条件的name字段值，可以在JSONPath末尾加上.name：

SELECT jsonb_path_query_array_tz(
  '[ { "name" : "oneDay", "start": "2021-07-02 00:00:00", "end": "2021-07-03 00:00:00" }, { "name" : "threeDays", "start": "2021-07-15 00:00:00", "ende": "2021-07-18 00:00:00" } ]'::jsonb,
  '$[*] ? ( coalesce(@.end, @.ende).datetime() - @.start.datetime() > "1 day" ).name'
);

关键细节解释 #

• 兼容end/ende键：用coalesce(@.end, @.ende)实现容错——优先读取end键的值，如果该键不存在，就读取ende的值，完美解决笔误问题；
• 时间类型转换与间隔计算：.datetime()函数将字符串格式的时间转换为JSONPath可计算的时间类型，减法操作会得到两个时间的间隔，再和"1 day"比较（JSONPath中字符串常量需要用双引号包裹）；
• 时区处理：jsonb_path_query_array_tz是带时区感知的函数，如果你输入的时间字符串没有指定时区，它会自动使用当前会话的时区进行解析，保证时间计算的准确性。

原SQL的问题点 #

1. 没有处理ende键，导致第二个对象的结束时间无法被读取，自然不会被筛选出来；
2. 时间间隔的写法1day在JSONPath中虽然也能识别，但用"1 day"的格式更规范，可读性更强。

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