0

I'm facing issues with a jsonb ARRAY column in PostgreSQL.
I need to sum this column for each row.

Sample values for the jsonb column

Expected Result:

index sum(snx_wavelenghts)
1 223123
2 223123
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228

2 Answers2

2

You can solve this ...

... with a subquery, then aggregate:

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?

fiddle

See:

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

You’ll need to extract the jsonb array contents from the jsonb array using jsonb_array_elements function before summing them. Here’s an example

SELECT SUM(w::float) AS wavelength_sum
FROM (
  SELECT jsonb_array_elements(snx_wavelengths) AS w 
  FROM my_table
);

This 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.html

ceckenrode
  • 4,543
  • 7
  • 28
  • 48