在 Postgres 中通過 json 數組（存儲為 jsonb）中的鍵對錶進行排序 (Order a table by a key in an array of json (stored as jsonb) in Postgres)

問題描述

在 Postgres 中通過 json 數組（存儲為 jsonb）中的鍵對錶進行排序 (Order a table by a key in an array of json (stored as jsonb) in Postgres)

我有一個 postgres，它有一個 jsonb 列，它是一個 jsons 數組。

id  array_json
123 [{"end_time": 22, "start_time": 21}]
124 [{"end_time": 22, "start_time": 9}, {"end_time": 20, "start_time": 0}]
126 [{"end_time": 22, "start_time": 0}]
125 [{"end_time": 22, "start_time": 1}, {"end_time": 20, "start_time": 6}]

我想在“start_time”之前訂購這些。一行有一個 json 數組。在這種情況下，排序必須考慮整個數組中最早的開始時間。如果兩個數組具有相同的開始時間，那麼哪個先出現並不重要。最終結果應該是：

id  array_json
126 [{"end_time": 22, "start_time": 0}]
124 [{"end_time": 22, "start_time": 9}, {"end_time": 20, "start_time": 0}]
125 [{"end_time": 22, "start_time": 1}, {"end_time": 20, "start_time": 6}]
123 [{"end_time": 22, "start_time": 21}]

我已經創建了 a這張桌子的小提琴。這應該怎麼做？

參考解法

方法 1:

This will work I think:

select id, array_json from the_table,
lateral (select min((d‑>>'start_time')::int) x from jsonb_array_elements(array_json) d) t(x)
order by x

Best regards, Bjarni

方法 2:

This query can be used:

select id, min(start_time) min_start_time, array_json 
from (
   select 
       id, 
       array_json,
       ‑‑ we must convert start_time to integer for right sort order
       (jsonb_array_elements(array_json)‑>>'start_time')::int4 as start_time
    from the_table
) tmp 
group by id, array_json
order by min_start_time;

(by user248884、Bjarni Ragnarsson、Slava Rozhnev)

參考文件

1. Order a table by a key in an array of json (stored as jsonb) in Postgres (CC BY‑SA 2.5/3.0/4.0)