PostgreSQL 9.6中SQLAlchemy查询JSONB数组日期匹配问题

问题场景 #

PostgreSQL 9.6数据库中有一张表，包含jsonb类型的列data，结构示例如下：

"data": {
  "element": [
    {"field": "2024-08-01", ...},
    {"field": "2024-08-02", ...}
  ]
}

需要通过SQLAlchemy编写查询，筛选出element数组中存在至少一个field日期在指定范围内的行。当前使用的函数无法正常工作：

def make_nested_date(query, value: dict):
    """
    Create nested date filter
    """
    date_from = isoparse(value.get('from'))
    date_to = isoparse(value.get('to'))
    query = query.where(
        and_(func.jsonb_array_elements(Table.c.data['element'])['field'].astext.cast(Date) >= date_from,
             func.jsonb_array_elements(Table.c.data['element'])['field'].astext.cast(Date) <= date_to)
    )
    return query

问题原因 #

原代码两次调用func.jsonb_array_elements，会将JSON数组展开成多行记录，两次展开的结果是独立的数据集，导致日期范围条件可能匹配不同的数组元素，无法正确筛选出同一元素满足日期范围的行，甚至返回错误结果或无结果。

解决方案 #

使用EXISTS子查询检查数组中是否存在至少一个元素满足日期范围条件，确保条件针对同一个数组元素判断：

from sqlalchemy import and_, exists, func, Date

def make_nested_date(query, value: dict):
    """
    Create nested date filter
    """
    date_from = isoparse(value.get('from'))
    date_to = isoparse(value.get('to'))
    
    # 定义EXISTS子查询，验证数组内是否有符合日期范围的元素
    exists_clause = exists().where(
        func.jsonb_array_elements(Table.c.data['element'])['field'].astext.cast(Date).between(date_from, date_to)
    )
    
    query = query.where(exists_clause)
    return query

备选实现（数组提取判断） #

如果需要做数组级别的日期判断（比如所有日期都在范围内），可以先将field提取为日期数组再操作，不过性能不如EXISTS方案：

def make_nested_date(query, value: dict):
    date_from = isoparse(value.get('from'))
    date_to = isoparse(value.get('to'))
    
    # 将JSON数组中的field转换为日期数组
    date_array = func.array_agg(
        func.jsonb_array_elements(Table.c.data['element'])['field'].astext.cast(Date)
    ).over(partition_by=Table.c.id)  # 按主键分组保证每个行对应自身的日期数组
    
    # 检查数组中是否存在目标日期范围内的元素
    query = query.where(
        func.array_to_string(date_array, ',').contains(
            func.generate_series(date_from, date_to, interval='1 day').cast(Date).cast(String)
        )
    )
    return query

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