PostgreSQL中JSONB字段DateValue筛选无结果问题排查

你的筛选无结果是因为JSON路径表达式中构造datetime的语法错误，而非日期格式问题（你使用的ISO 8601格式是符合要求的）。以下是两种可行的解决方法：

方法一：修正JSON路径表达式 #

将原语句中构造datetime的方式从"日期字符串".datetime()改为datetime("日期字符串")，这是PostgreSQL JSON路径的正确语法：

SELECT id, reference, custom_fields_json
FROM master.masters
WHERE custom_fields_json @? '$[*] ? (@.DateValue.datetime() > datetime("2022-06-22T03:09:37Z"))';

方法二：数组展开+原生类型转换（推荐） #

通过展开JSON数组，将DateValue字符串转换为PostgreSQL原生的带时区时间戳进行比较，逻辑更直观，也便于调试：

SELECT DISTINCT m.id, m.reference, m.custom_fields_json
FROM master.masters m
JOIN jsonb_array_elements(m.custom_fields_json) cf ON true
WHERE (cf->>'DateValue')::timestamp with time zone > '2022-06-22T03:09:37Z'::timestamp with time zone;

这里使用timestamp with time zone适配你日期字符串中的Z（UTC时区标识），避免时区转换带来的误差，DISTINCT用于去除因数组展开产生的重复行。

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