I have a table t in postgres database. It has a column data which contains jsonb data in the following format (for each record)-
{
"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"] }
}
where 20161214 is the date, "4" is the month, 642 is the amount.
I need to find the minimum amount for each record of the table and the month that amount belongs to.
What I have tried:
Using jsonb_each function and separating key value pairs and then using min function.But still I cant get the month it belongs to.
How can this be achieved?
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 |
+----------+-------+--------+