如何在PostgreSQL中读取并比较嵌套JSON数组对象中的日期？

要筛选data列中user_node_assignments数组里assignment_end_date大于等于指定日期的记录，你需要先展开JSON数组，提取日期字段并转换为可比较的时间戳类型，再进行条件判断。以下是两种可行的实现方式：

方法一：使用EXISTS子查询（推荐，无重复行） #

这种方式会检查每条记录的JSON数组中是否存在满足条件的元素，不会生成重复结果，性能更高效：

SELECT id, data
FROM "fq_DateUserPerformance" fdup2
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(fdup2.data->'user_node_assignments') AS assignments
    WHERE (assignments->>'assignment_end_date')::timestamptz >= '2022-03-18T17:09:55.176822+01:00'::timestamptz
);

方法二：使用LATERAL JOIN + DISTINCT #

通过横向连接展开数组元素，再通过DISTINCT去重重复的主表记录：

SELECT DISTINCT fdup2.id, fdup2.data
FROM "fq_DateUserPerformance" fdup2
JOIN LATERAL jsonb_array_elements(fdup2.data->'user_node_assignments') AS assignments ON true
WHERE (assignments->>'assignment_end_date')::timestamptz >= '2022-03-18T17:09:55.176822+01:00'::timestamptz;

关键说明： #

• jsonb_array_elements：将JSON数组拆分为多行，每个数组元素对应一行记录。
• ->>：提取JSON字段的文本值（区别于->返回JSON类型），方便后续转换为时间戳。
• ::timestamptz：将文本日期转换为带时区的时间戳类型，确保日期比较的准确性（适配你数据中的时区格式）。

内容的提问来源于stack exchange，提问作者Saad Ul Hassan