Calculating Weighted Average of JSONB Array of Keys

user_89904 0
Viewed 30

In one of my tables I have the following structure,

ID  LOG 
1   [{"x":12,"W":1}, {"x":24,"W":2}]
2   [{"x":14,"W":1.02}, {"x":20,"W":2.2}, {"x":11,"W":1.022}, {"x":45,"W":62.2}]

I am calculating weighted average of x on application side, I would like to create a Generated Column called weighted_avg_x that is updated every time data is appended to LOG column. Two questions can it be done using generated columns also I am kind of lost on the jsonb notation to calculate sum(x*w)/sum(w)?

jsonb postgresql json database
1 Answers

You need an immutable function that may be used to define generated column:

create or replace function get_weighted_avg_x(log jsonb)
returns numeric language sql immutable as
$$
    select sum((elem->'x')::numeric* (elem->'W')::numeric) / sum((elem->'W')::numeric)
    from jsonb_array_elements(log) as arr(elem)
$$;

alter table my_table add column weighted_avg_x numeric
    generated always as (get_weighted_avg_x(log)) stored;

The solution is possible in Postgres 12+. In earlier versions you need to define a trigger.

Test it in db<>fiddle.

user_19957380
Related