PostgreSQL:如何对包括JSONB字段的属性求和，并保持表形状? [英] PostgreSQL: How to SUM attributes including a JSONB field, and retain table shape?

问题描述

我正在使用Postgres 9.4.我有一个带有JSONB字段的表:

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                │

我想按日期检索各种属性的总和值，包括JSONB数组中的任何内容(可以安全地假定其深度为1)，并以与原始表相同的结构返回这些值.

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;

它不会失败，并且JSON作为字典呈现，这就是我想要的方式.但是，total_list_size的总和看起来太大.我认为必须将其相加两次.

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.

如何在保持相同形状结果的同时获得正确的total_list_size求和值?

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

推荐答案

子查询中的函数jsonb_each_text()导致列total_list_size被复制的次数与star_pu中的项目数相同，因此avg()显示正确的结果.

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.

要为date获得一个total_list_size，可以使用一个并行子查询来独立累加该值.

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:如何对包括JSONB字段的属性求和，并保持表形状?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！