Paul Arterburn Paul Arterburn - 3 months ago 13
SQL Question

Sum all of the items in a JSONB array field

If my table is setup like:

indicators:
id: 56789,
funding (JSONB): [
{
amount: 345678
},
{
amount: 7899
}
]


I can successfully sum the first amounts on every record with:

Rails —
Indicator.sum("(funding->0->>'amount')::float")


SQL —
SELECT SUM((funding->0->>'amount')::float) FROM "indicators"


How would you query a sum of all of the
amounts
(not just the 0 index items)?


Running Rails 5 & Postgres 9.5.4.

Note: This post is similar to How do I query using fields inside the new PostgreSQL JSON datatype? - but I'm looking for a way to iterate over each of the array elements to sum them (as opposed to calling them directly by an index number).

Update...
Thanks to @klin's answer below, I was able to put together the Rails version that gave me the aggregate total:

Indicator.joins("cross join lateral jsonb_array_elements(funding)").sum("(value->>'amount')::float")

Answer

Use jsonb_array_elements()

select sum((value->>'amount')::float)
from indicators
cross join lateral jsonb_array_elements(funding)