CODEWITHSUNDEEP
jsonpostgresqlaverage
Jordash
Jordash

Reputation: 3103

PostgreSQL average of JSON Data

I have some data like this:

id      heart_rate
1       {0: 28, 1: 25, 2: 38, 3: 42}
2       {0: 30, 1: 28, 2: 43, 3: 58}
3       {0: 42, 1: 29, 2: 98, 3: 38}

I'm trying to return an object with the averaged values, something like this:

{0: 32, 1: 26, 2: 58, 3: 43}

I tried script to loop through and analyze, but with the amount of data a loop through this could take too long and not be practical.

Upvotes: 0

Views: 245

Answers (1)

user330315
user330315

Reputation:

You need to extract all values, cast them to a number, calculate the average and then convert it back to a JSON value:

select to_jsonb(r)
from (
  select avg((heart_rate ->> '0')::int) as "0", 
         avg((heart_rate ->> '1')::int) as "1", 
         avg((heart_rate ->> '2')::int) as "2", 
         avg((heart_rate ->> '3')::int) as "3"
  from the_table
) r;

If you don't really know the keys, but you know that all of them can be cast to a number, you could do something like this:

select jsonb_object_agg(ky, average)
from (
  select r.ky, round(avg(r.val::int)) as average
  from the_table
    cross join jsonb_each(heart_rate) as r(ky, val)
  group by r.ky
) t;

Online example

Upvotes: 2

Related Questions