By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table the_table
(
id integer primary key,
the_column jsonb
);
insert into the_table
values
(1, '[ { "state" : "CA", "tax_amount" : 3},{ "state" : "AZ", "tax_amount" : 4}]'),
(2, '[ { "state" : "CA", "tax_amount" : 10},{ "state" : "AZ", "tax_amount" : 12},{ "state" : "BY", "tax_amount" : 5}]');
2 rows affected
select t.id,
(select sum((item ->> 'tax_amount')::int)
from jsonb_array_elements(t.the_column) as x(item)) as total_tax
from the_table t;
| id | total_tax |
|---|---|
| 1 | 7 |
| 2 | 27 |