从jsonb对象postgres 11中检索特定值

Question

我的表requests中有以下jsonb列，称为任务

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|         id  |           tasks                                                                                                                                      |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1           | [{"name":"Pick up","order":0,"end_at":null,"start_at":"2022-10-28T13:00:00.000+08:00"}, {"name":"Deliver","order":1,"end_at":"2022-10-28T13:42:00.000+08:00","start_at":"2022-10-28T13:27:00.000+08:00"}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

下面是该表的一个示例，我想获得第一个对象的start_at，其中order=0

我可以在postgres 12中轻松做到这一点，但正在寻找一种在Postgres11 中做到的方法

select (jsonb_path_query_array(jsonb_agg(row_to_json(jb)), '$.tasks[0].start_at'))[0] from requests as jb where id = 1;返回"2022-10-28T13:00:00.000+08:00"

在postgres 11中我该怎么做？

我尝试使用json_agg，但只能得到对象，而不能得到值select to_jsonb(jsonb_agg(row_to_json(jb))->0->'tasks'->>0)->>0 from requests as jb where id = 1;

Answer 1

嗯，它甚至更简单。首先展平JSONB阵列。

select l ->> 'start_at' as start_at 
from requests, lateral jsonb_array_elements(tasks) as l
where l ->> 'order' = '0'
order by l ->> 'start_at' limit 1; -- get the first (oldest) one only

DB fiddle