how are you?

I needed to store an array of numbers as JSONB in PostgreSQL.

Now I'm trying to calculate stats moments from this JSON, I'm facing some issues.

Sample of my data:

I already was able to convert a JSON into a float array.

I used a function to convert jsonb to float array.

CREATE OR REPLACE FUNCTION jsonb_array_castdouble(jsonb) RETURNS float[] AS $f$
    SELECT array_agg(x)::float[] || ARRAY[]::float[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

Using this SQL:

with data as (
        select
        s.id as id,
        jsonb_array_castdouble(s.snx_normalized) as serie
        FROM
          spectra s
)

select * from data;

I found a function that can do these calculations and I need to pass an array for that:

I already tried to use unnest, but it will get only one value, not the entire array :(.

My goal is:

Be able to apply stats moment (kurtosis, skewness) for each row.

like:

Bonus: There is a way to not use this 'with data', use the transformation in the select statement?

CodePudding user response：

snx_wavelengths is JSON, right? And also you provided it as a picture and not text :( the data looks like (id, snx_wavelengths) - I believe you meant id saying index (not a good idea to use a keyword, would require identifier doublequotes):

1,[1,2,3,4]
2,[373,232,435,84]

If that is right:

select id, (stats_agg(v::float)).skewness
from myMeasures,
     lateral json_array_elements_text(snx_wavelengths) v
group by id;

DBFiddle demo

BTW, you don't need "with data" in the original sample if you don't want to use and could replace with a subquery. ie:

select (stats_agg(n)).* from (select unnest(array[16,22,33,24,15])) data(n)
union all
select (stats_agg(n)).* from (select unnest(array[416,622,833,224,215])) data(n);

EDIT: And if you needed other stats too:

select id, "count","min","max","mean","variance","skewness","kurtosis"
from myMeasures,
     lateral (select (stats_agg(v::float)).* from json_array_elements_text(snx_wavelengths) v) foo
group by id,"count","min","max","mean","variance","skewness","kurtosis";

DBFiddle demo