SELECT index, sum(nr) AS wavelength_sum
FROM (
SELECT index, jsonb_array_elements(snx_wavelengths)::numeric AS nr
FROM tbl
) sub
GROUP BY 1
ORDER BY 1; -- optional?
… with an aggregate in a correlated subquery:
SELECT index
, (SELECT sum(nr::numeric) FROM jsonb_array_elements(snx_wavelengths) nr) AS wavelength_sum
FROM tbl
ORDER BY 1; -- optional?
… or with an aggregate in a LATERAL subquery:
SELECT t.index, js.wavelength_sum
FROM tbl t
LEFT JOIN LATERAL (
SELECT sum(nr::numeric) AS wavelength_sum
FROM jsonb_array_elements(t.snx_wavelengths) nr
) js ON true
ORDER BY 1; -- optional?
Your screenshot shows fractional digits. Cast to the type numeric to get exact results. A floating point type like real or float can introduce rounding errors.
2
Answers
You’ll need to extract the jsonb array contents from the jsonb array using
jsonb_array_elements
function before summing them. Here’s an exampleThis should work if I remember correctly (remember to update
my_table
to your table name). More info here https://www.postgresql.org/docs/9.5/functions-json.htmlYou can solve this …
… with a subquery, then aggregate:
… with an aggregate in a correlated subquery:
… or with an aggregate in a
LATERAL
subquery:fiddle
See:
Your screenshot shows fractional digits. Cast to the type
numeric
to get exact results. A floating point type likereal
orfloat
can introduce rounding errors.