PostgreSQL: How to SUM attributes including a JSONB field, and retain table shape?

Question

I am working with Postgres 9.4. I have a table with a JSONB field:

     Column      │         Type         │                             Modifiers
─────────────────┼──────────────────────┼────────────────────────────────────────────────────────────────────
 id              │ integer              │ not null default
 practice_id     │ character varying(6) │ not null
 date            │ date                 │ not null
 pct_id          │ character varying(3) │
 astro_pu_items  │ double precision     │ not null
 astro_pu_cost   │ double precision     │ not null
 star_pu         │ jsonb                │

I want to retrieve the summed values by date of various attributes, including anything in the JSONB array (which it is safe to assume has depth 1), and return the values in the same structure as in the original table.

This is the query I'm using right now:

SELECT date,
       SUM(total_list_size) AS total_list_size,
       json_object_agg(key, val)
FROM (
    SELECT date,
           SUM(total_list_size) AS total_list_size,
           key, SUM(value::numeric) val
    FROM frontend_practicelist p, jsonb_each_text(star_pu)
    GROUP BY date, key
    ) p
GROUP BY date
ORDER BY date;

It does not fail, and the JSON is presented as a dictionary which is how I want it. However, the summed value of total_list_size looks hugely too large. I think it must be being summed twice.

How can I get the correct summed value for total_list_size, while retaining the same shape results?

Update: If I substitute AVG for SUM in the outer query, I get the right results, although I'm not sure this is the most efficient way to do things. — Richard

klin klin · Accepted Answer · 2016-02-01T16:57:02

The function jsonb_each_text() in the subquery causes the column total_list_size is replicated as many times as number of items in star_pu, so avg() shows a proper result.

To get one total_list_size for a date you can use a parallel subquery that accumulates the value independently.

select *
from (
    select date, json_object_agg(key, val) total_star_pu
    from (
        select date, key, sum(value::numeric) val
        from frontend_practicelist, jsonb_each_text(star_pu)
        group by date, key
        ) s
    group by date
    ) s
    join (
        select date, sum(total_list_size) total_list_size
        from frontend_practicelist
        group by date
        ) t
    using(date)
order by date;

PostgreSQL: How to SUM attributes including a JSONB field, and retain table shape?

2 Answers