如何利用PostgreSQL函数返回的JSON数组按日期筛选seq_no？

问题背景 #

我有一个PostgreSQL函数，它以文本格式返回如下JSON数组：

[{"seq_no":5796,"start_date":null,"end_date":"2008-09-30 12:32:28","geom_change":"Y"}, 
 {"seq_no":8235,"start_date":"2008-09-30 12:32:28","end_date":"2008-10-02 16:43:24","geom_change":"N"}, 
 {"seq_no":9306,"start_date":"2008-10-02 16:43:24","end_date":"2008-10-02 18:31:09","geom_change":"N"}, 
 {"seq_no":9754,"start_date":"2008-10-02 18:31:09","end_date":"2008-10-07 17:08:25","geom_change":"N"}, 
 {"seq_no":10701,"start_date":"2008-10-07 17:08:25","end_date":"2008-10-08 15:17:48","geom_change":"N"}, 
 {"seq_no":8940,"start_date":"2008-10-08 15:17:48","end_date":"2008-10-08 15:51:47","geom_change":"N"}, 
 {"seq_no":12500,"start_date":"2008-10-08 15:51:47","end_date":"2008-10-08 17:34:35","geom_change":"N"}, 
 {"seq_no":13079,"start_date":"2008-10-08 17:34:35","end_date":"2008-10-08 17:56:03","geom_change":"N"}]

我需要基于这些数据，按start_date和end_date筛选seq_no，最终得到如下格式的表格结果：

同时想了解是否有更简便的方法实现按起止日期筛选seq_no？

基础实现方案 #

首先需要将函数返回的文本格式JSON解析为结构化数据，再进行筛选。假设函数名为get_target_data()，可以用json_array_elements拆分JSON数组，再提取字段并转换类型：

SELECT
    (elem->>'seq_no')::INT AS seq_no,
    elem->>'start_date' AS start_date,
    elem->>'end_date' AS end_date,
    elem->>'geom_change' AS geom_change
FROM
    (SELECT json_array_elements(get_target_data()::JSON) AS elem) AS parsed_data
WHERE
    -- 示例筛选条件：end_date不晚于指定时间
    (elem->>'end_date')::TIMESTAMP <= '2008-10-02 16:43:24';

更简便的实现方法 #

使用json_to_recordset可以直接将JSON数组映射为关系型数据行，无需手动逐个提取字段，代码更简洁且性能更优：

SELECT *
FROM json_to_recordset(get_target_data()::JSON) AS x(
    seq_no INT,
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    geom_change TEXT
)
WHERE
    -- 根据需求调整筛选条件，比如筛选日期范围
    end_date <= '2008-10-02 16:43:24';

额外优化建议 #

如果可以修改原函数，建议直接返回JSON或JSONB类型而非文本，这样可以省去类型转换的开销，进一步提升效率。

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