PostgreSQL: create aggregate method iterating over json
$10-30 USD
Cancelled
Posted over 7 years ago
$10-30 USD
Paid on delivery
Using the data below
```
create table my_table (my_column json);
insert into my_table values
('{"total": 10, "custom": 1}'::json),
('{"total": 9, "custom": 2}'::json),
('{"total": 21, "custom": 7}'::json),
('{"total": 11, "custom": 9}'::json),
('{"total": 12, "custom": 10}'::json);
```
Objective: Create 3 methods to aggregate the data in the manner below.
```
select max_json_key('total', my_column) from my_table;
# results in {"total": 21, "custom": 7}
select min_json_key('total', my_column) from my_table:
# results in {"total": 9, "custom": 2}
select avg_json_key('total', my_column) from my_table:
# results in {"total": 12, "custom": 10}
```
- The resulting value returns the full json object for the corresponding value.
- keys are not aggregated, only the "totals" key is used to determine which entry fits the criteria.
Thank you!