PostgreSQL中按日期检测JSONB数组内messages是否非空

我来帮你搞定这个JSONB结构的查询需求！你的目标是按日期分组，判断每个日期下是否存在至少一个messages数组非空（只要有一个非空就返回true，全空则返回false），原代码的子查询逻辑和JSON处理方式有几个问题，咱们来修正一下：

问题分析 #

原代码的主要问题：

• 子查询里直接用fields ->> 'messages'会把数组转成字符串，没法用jsonb_array_length正确判断长度
• 判断jsonb_array_length(...) = 1逻辑有误，应该判断长度大于0（只要数组有元素就算非空）
• 没有用合适的聚合函数按日期汇总判断结果

正确的SQL写法 #

SELECT 
    t2.date,
    bool_or(jsonb_array_length(fields_obj -> 'messages') > 0) AS has_messages
FROM 
    table1 t1
INNER JOIN table2 t2 ON t2.id = t1.id
-- 展开动态level1键对应的数组
CROSS JOIN jsonb_object_keys(t1.result) AS root_node
CROSS JOIN jsonb_array_elements(t1.result -> root_node) AS level2_obj
-- 展开level2数组
CROSS JOIN jsonb_array_elements(level2_obj -> 'level2') AS fields_obj
GROUP BY 
    t2.date;

代码解释 #

1. 遍历动态level1键：用jsonb_object_keys(t1.result)获取所有动态的level1键名，再通过jsonb_array_elements展开对应的数组元素
2. 展开level2数组：对每个level1数组元素，展开其level2字段对应的数组
3. 判断messages是否非空：jsonb_array_length(fields_obj -> 'messages') > 0返回布尔值，标记当前messages数组是否有内容
4. 按日期聚合判断：用bool_or聚合函数，只要该日期下有任何一个messages数组非空就返回true；如果所有messages都为空数组，则返回false

如果需要更明确的布尔结果展示，可以用CASE语句包装：

SELECT 
    t2.date,
    CASE WHEN bool_or(jsonb_array_length(fields_obj -> 'messages') > 0) 
         THEN true 
         ELSE false 
    END AS has_messages
FROM 
    table1 t1
INNER JOIN table2 t2 ON t2.id = t1.id
CROSS JOIN jsonb_object_keys(t1.result) AS root_node
CROSS JOIN jsonb_array_elements(t1.result -> root_node) AS level2_obj
CROSS JOIN jsonb_array_elements(level2_obj -> 'level2') AS fields_obj
GROUP BY 
    t2.date;

这样就能完美实现你要的按日期检测的需求啦！

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