Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

drop table if exists my_table;
create table my_table (id int primary key, log jsonb);

insert into my_table values
(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}]');
2 rows affected

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;
select *
from my_table;


id
log
weighted_avg_x


1
[{"W": 1, "x": 12}, {"W": 2, "x": 24}]
20.0000000000000000

2
[{"W": 1.02, "x": 14}, {"W": 2.2, "x": 20}, {"W": 1.022, "x": 11}, {"W": 62.2, "x": 45}]
43.1733241022244966

id	log	weighted_avg_x
1	[{"W": 1, "x": 12}, {"W": 2, "x": 24}]	20.0000000000000000
2	[{"W": 1.02, "x": 14}, {"W": 2.2, "x": 20}, {"W": 1.022, "x": 11}, {"W": 62.2, "x": 45}]	43.1733241022244966