aggregate all keys(deep) of json

user_11974363 0
Viewed 54

i have a table like this (9,000,000row/day)(2,000,000row/country) partition by data_date->country(sub_partition)

enter image description here (markdown has a problem,i can't submit this table,so->image)

i want to aggregate by all the keys of category_tree to sum sold like this

category_id data_date sum_sold
a 2022-07-10 53
b 2022-07-10 53
c 2022-07-10 53
d 2022-07-10 28
e 2022-07-10 20

i must get the different level of category and last 7 or more days(data_date) on in once select

i used the sql like this

    SELECT 'a' AS category_id, data_date, sum(sold) AS sum_sold
    FROM table
    WHERE CAST((category_tree::jsonb#>'{"a"}') AS jsonb) IS NOT NULL 
    AND data_date>= '2022-07-01' 
    AND data_date <= '2022-07-09'
    GROUP BY data_date
    UNION ALL

or this

but this sql can't get different level of category

    SELECT jsonb_object_keys(category_tree::jsonb#>'{"a", "b"}') as category_id, 
    data_date, sum(sold) AS sum_sold 
    FROM table 
    WHERE data_date>= '2022-07-01' 
    AND data_date <= '2022-07-09' 
    AND CAST((category_tree::jsonb#>'{"a", "b", "c"}') AS jsonb) IS NOT NULL
    GROUP BY data_date

but by the way i have to face two problems with ''union all'->

1.too many range table entries

2.ERROR: at most 50 slices are allowed in a query, current number: 177 HINT: rewrite your query or adjust GUC gp_max_slices

greenplum jsonb aggregate-functions sql
1 Answers

You can use a recursive cte with json_each:

with recursive cte(d, k, v, s) as (
   select c.data_date, v.key, v.value, c.sold from countries c cross join json_each(c.category_tree) v
   union all
   select c.d, v.key, v.value, c.s from cte c cross join json_each(c.v) v
)
select c.k, min(c.d), sum(c.s) from cte c group by c.k order by c.k
user_73267380
Related