后发现以在包含JSONB数据的Postgres SQL列中找到最小值

Question

我在Postgres数据库中有一个表t。它具有一个列data，该列以以下格式包含JSONB数据（对于每个记录） -

{
  "20161214": {"4": ["3-14", "5-16", "642"], "9": ["3-10", "5-10", "664"] },
  "20161217": {"3": ["3-14", "5-16", "643"], "7": ["3-10", "5-10", "661"] } 
}

其中 20161214是日期， "4"是一个月， 642是金额。

我需要找到表的每个记录和属于的月份的最低金额。

我尝试的是：

使用jsonb_each函数并分离键值对，然后使用min函数。但是我仍然无法获得它属于的月份。

如何实现？

Answer 1

select  j2.date
       ,j2.month
       ,j2.amount
from    t 
        left join lateral  
           (select      j1.date
                       ,j2.month
                       ,(j2.value->>2)::numeric  as amount
            from        jsonb_each (t.data) j1 (date,value) 
                        left join lateral jsonb_each (j1.value) j2 (month,value)
                        on true
            order by    amount
            limit       1   
            ) j2
        on true

---

+----------+-------+--------+
| date     | month | amount |
+----------+-------+--------+
| 20161214 | 4     | 642    |
+----------+-------+--------+

Answer 2

替代（无连接）：

select
    min(case when amount = min_amount then month end) as month,
    min_amount as amout
from (
    select
        key as month,
        (select min((value->>2)::int) from jsonb_each(value)) as amount,
        min((select min((value->>2)::int) from jsonb_each(value))) over(partition by rnum) as min_amount,
        rnum
    from (
        select
            (jsonb_each(data)).*,
            row_number() over() as rnum
        from t
    ) t
) t
 group by
     rnum, min_amount;