skip to Main Content

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

2

Answers


  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

    Login or Signup to reply.
  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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search