PostgreSQL查询数组字段内JSON对象的程序报错解决

问题背景 #

需求：查询当天的programs，需满足：

• 日程匹配或无日程
• 存在session满足：day等于当前日，且当前时间处于start_time加减entry_time_range（示例为30分钟）区间内
• 当前日期处于start_line和deadline之间
• 匹配指定facility_id

用户原查询语句：

SELECT *
      FROM programs
      WHERE EXISTS (
          SELECT *
          FROM json_array_elements(sessions)  AS session
          WHERE
            session->>'day' = :day 
            AND :currentDate::time BETWEEN (session->>'start_time'::time - (entry_time_range || ' minutes')::interval)::time
            AND (session->>'start_time'::time + (entry_time_range || ' minutes')::interval)::time
        )
             
      AND( filter IS NULL
          OR filter LIKE :schedule
        )
      AND :currentDate BETWEEN startline AND deadline       
      AND facility_id = :facility_id;

执行报错：

function json_array_elements(json[]) does not exist

program表示例：

id | start_line | deadline | filters | facility_id | entry_time_range | sessions
---------------------------------------------------------------------------------
2 | 2023-04-03 03:00:00+03 | 2023-04-03 03:00:00+03 | {"in":[{"schedule":[["de682021-c99a-47a5-bc3a-8e089e033732"]]}]} | 'some_id' | 30 | {"{\"day\":\"Monday\",\"start_time\":\"06:00:00\",\"finish_time\":\"07:00:00\"}","{\"day\":\"Thursday\",\"start_time\":\"16:11:00\",\"finish_time\":\"23:00:00\"}"}

报错原因 #

从表结构示例可以看出，sessions字段是存储JSON字符串的文本数组（text[]类型），而非标准的json或jsonb类型。json_array_elements()函数仅接受JSON类型参数，直接传入数组类型会导致函数不存在的报错。

另外原语句存在两处语法问题：

1. 类型转换顺序错误：session->>'start_time'::time应该先提取文本再转时间，即(session->>'start_time')::time
2. 字段名拼写错误：表中字段为filters，原语句写为filter

修正后的查询语句 #

SELECT *
FROM programs
WHERE EXISTS (
    SELECT 1
    FROM unnest(sessions) AS session_str
    -- 将文本数组元素转换为JSON对象
    , LATERAL (SELECT session_str::json AS session) AS j
    WHERE
        session->>'day' = :day
        AND :currentDate::time BETWEEN 
            ((session->>'start_time')::time - (entry_time_range || ' minutes')::interval)::time
            AND ((session->>'start_time')::time + (entry_time_range || ' minutes')::interval)::time
)
AND (filters IS NULL OR filters @> :schedule)
AND :currentDate BETWEEN start_line AND deadline
AND facility_id = :facility_id;

关键修改说明 #

1. 处理文本数组：用unnest(sessions)将文本数组拆分为单个JSON字符串元素，再通过LATERAL子查询转换为JSON对象
2. 修正时间计算：调整类型转换顺序，确保先提取start_time文本再转换为time类型后进行区间运算
3. 修正字段名：将filter改为表中实际字段名filters，同时用@>操作符替代LIKE，更适合JSON类型的匹配（若:schedule是JSON格式）
4. 优化子查询：子查询用SELECT 1替代SELECT *，提升查询效率

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