Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (
  index int PRIMARY KEY
, snx_wavelengths jsonb
);

INSERT INTO tbl VALUES
  (1, '[1.1, 2.2, 3.3]')
, (2, '[2, 3, 4.5]')
;

CREATE TABLE



INSERT 0 2


-- subquery
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?


index
wavelength_sum


1
6.6

2
9.5



SELECT 2


-- correlated subquery
SELECT index
    , (SELECT sum(nr::numeric) FROM jsonb_array_elements(snx_wavelengths) nr) AS wavelength_sum
FROM   tbl
ORDER  BY 1;  -- optional?


index
wavelength_sum


1
6.6

2
9.5



SELECT 2


-- 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?


index
wavelength_sum


1
6.6

2
9.5



SELECT 2