pgx扫描含daterange的JSON对象报错：无法解析为pgtype.Range类型

问题核心：当使用json_build_object和json_agg聚合数据时，PostgreSQL会把daterange类型隐式转换为字符串格式（如[2020-02-01,2023-02-03)），但pgtype.Range[pgtype.Date]默认仅实现了sql.Scanner接口（用于直接扫描PostgreSQL原生daterange类型），并未实现json.Unmarshaler接口，导致JSON解析器无法将字符串直接反序列化为该类型。

优先推荐的解决方案是给pgtype.Range[pgtype.Date]添加自定义JSON反序列化支持，步骤如下：

1. 自定义包装类型并实现JSON反序列化 #

创建一个包装pgtype.Range[pgtype.Date]的自定义类型，实现json.Unmarshaler接口来解析PostgreSQL的daterange字符串格式：

import (
    "encoding/json"
    "github.com/jackc/pgx/v5/pgtype"
)

// DateRange 包装pgtype.Range[pgtype.Date]，添加JSON反序列化能力
type DateRange pgtype.Range[pgtype.Date]

// UnmarshalJSON 解析PostgreSQL daterange字符串格式为DateRange
func (dr *DateRange) UnmarshalJSON(data []byte) error {
    // 先将JSON字符串解析为Go原生字符串（去除JSON引号）
    var rangeStr string
    if err := json.Unmarshal(data, &rangeStr); err != nil {
        return err
    }

    // 利用pgtype.Range自带的Scan方法解析daterange字符串
    var pgRange pgtype.Range[pgtype.Date]
    if err := pgRange.Scan(rangeStr); err != nil {
        return err
    }

    // 将解析后的pgtype.Range转换为自定义类型
    *dr = DateRange(pgRange)
    return nil
}

// 可选：实现MarshalJSON以支持将DateRange序列化为JSON字符串
func (dr DateRange) MarshalJSON() ([]byte, error) {
    pgRange := pgtype.Range[pgtype.Date](dr)
    var rangeStr string
    if err := pgRange.AssignTo(&rangeStr); err != nil {
        return nil, err
    }
    return json.Marshal(rangeStr)
}

2. 修改Go结构体定义 #

将B结构体中的Period字段替换为自定义的DateRange类型：

type A struct {
    ID     int
    Name   *string
    SomeBs []B
}

type B struct {
    ID     int
    Title  *string
    Period DateRange // 使用自定义类型替代pgtype.Range[pgtype.Date]
}

3. 保持原有查询语句不变 #

你的查询语句无需修改，现在pgx可以正常将JSON中的daterange字符串扫描到DateRange字段中，该类型本质与pgtype.Range[pgtype.Date]兼容，完全不影响后续对日期范围的操作。

替代方案（无需自定义类型） #

如果不想自定义类型，也可以在查询时将daterange拆分为结构化的JSON对象：
修改查询中的json_build_object部分，把period拆分为边界值和包含状态：

SELECT 
    a.id,
    a.name,
    bs.*
FROM first_table a
LEFT JOIN LATERAL (
    SELECT 
    COALESCE(json_agg(json_build_object(
        'id', id,
        'title', title,
        'lower', lower(period),
        'upper', upper(period),
        'lower_inc', lower_inc(period),
        'upper_inc', upper_inc(period)
    )), '[]')
    FROM second_table b 
    WHERE a.id = b.a_id
) AS bs ON true 

此时JSON中的period是包含lower、upper等字段的对象，你需要额外实现pgtype.Range[pgtype.Date]的反序列化逻辑来匹配这些字段，但这种方式不如第一种方案简洁。

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