如何用PostgreSQL JSON函数查询含bar事件的汽车及最新事件时间？

完全可以利用PostgreSQL提供的JSON/JSONB函数实现这个需求，以下是具体的查询方案：

假设你的表名为car_events，events字段类型为jsonb（推荐使用jsonb，比json有更好的查询性能和索引支持；如果是json类型，只需把下面语句中的jsonb替换为json即可）：

SELECT 
    brand,
    MAX((event_obj ->> 'timestamp')::timestamp) AS last_bar_timestamp
FROM 
    car_events,
    jsonb_array_elements(events -> 'data') AS event_obj
WHERE 
    (event_obj ->> 'event') = 'bar'
GROUP BY 
    brand;

语句解释： #

1. 展开JSON数组：jsonb_array_elements(events -> 'data') AS event_obj 将events字段里的data数组拆分成独立的JSON对象行，每个对象对应一个事件。
2. 筛选bar事件：(event_obj ->> 'event') = 'bar' 提取每个事件对象中event字段的文本值，只保留事件类型为bar的记录。
3. 聚合最新时间：MAX((event_obj ->> 'timestamp')::timestamp) 将JSON中的时间字符串转换为PostgreSQL的timestamp类型，再通过MAX()函数获取每个品牌最新的bar事件时间。
4. 按品牌分组：GROUP BY brand 确保每个品牌只返回一行结果，对应其最新的bar事件时间。

如果需要严格匹配输出格式（保持时间字符串的原格式），可以把聚合后的timestamp再转回字符串：

SELECT 
    brand,
    TO_CHAR(MAX((event_obj ->> 'timestamp')::timestamp), 'YYYY-MM-DD"T"HH24:MI:SS') AS last_bar_timestamp
FROM 
    car_events,
    jsonb_array_elements(events -> 'data') AS event_obj
WHERE 
    (event_obj ->> 'event') = 'bar'
GROUP BY 
    brand;

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