PostgreSQL 12中根据JSON列值查询orders表指定时间范围数据

实现逻辑 #

• 展开updatesJSON数组内的所有元素为独立行
• 过滤出所有状态流转为closed的记录项
• 提取关闭记录对应的ISO格式时间，转换为PostgreSQL支持的时间戳类型
• 取每个订单的最晚关闭时间，判断与当前时间的间隔不超过5天

可用查询语句 #

如果updates列的类型为jsonb（如果是json类型，把下文所有jsonb替换为json即可），可直接使用以下SQL：

SELECT o.*
FROM orders o
CROSS JOIN LATERAL (
    SELECT MAX((update_item -> 'time' ->> 'iso')::timestamptz) AS last_closed_time
    FROM jsonb_array_elements(o.updates) AS update_item
    WHERE update_item ->> 'to' = 'closed'
) AS closed_record
WHERE closed_record.last_closed_time >= NOW() - INTERVAL '5 days';

额外说明 #

• 从未关闭过的订单会自动被过滤，因为对应的last_closed_time为NULL，不满足WHERE条件
• 如果业务使用非UTC时区，可自行增加时区转换逻辑，比如(update_item -> 'time' ->> 'iso')::timestamptz AT TIME ZONE 'Asia/Shanghai'替换成对应时区即可
• 数据量较大时可给updates列建GIN索引，也可以把最后关闭时间冗余为单独字段存储，大幅提升查询效率

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