PostgreSQL: как СУММИРОВАТЬ все атрибуты в поле JSONB?

Я работаю с Postgres 9.4. У меня есть поле JSONB:

     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:

SELECT star_pu FROM mytable limit 1;
star_pu │ {"statins_cost": 16790.692924903742, "hypnotics_adq": 18523.58385328709, "laxatives_cost": 8456.98405165182, "analgesics_cost": 48271.21822239242, "oral_nsaids_cost": 9911.336052088493, "antidepressants_adq": 186715.7, "antidepressants_cost": 26885.54622478343, "bronchodilators_cost": 26646.54899847902, "cox-2_inhibitors_cost": 2063.4652015406728, "antiplatelet_drugs_cost": 4844.798321177439, "drugs_for_dementia_cost": 3390.569564110721, "antiepileptic_drugs_cost": 44990.94756286502, "oral_antibacterials_cost": 21047.048353859234, "oral_antibacterials_item": 5096.6501798218205, "ulcer_healing_drugs_cost": 15999.05326260261, "lipid-regulating_drugs_cost": 24711.589440943662, "proton_pump_inhibitors_cost": 14545.398978447573, "inhaled_corticosteroids_cost": 50759.91062192373, "calcium-channel_blockers_cost": 11571.457036131978, "omega-3_fatty_acid_compounds_adq": 2026.0, "benzodiazepine_caps_and_tabs_cost": 1800.2581325567717, "bisphosphonates_and_other_drugs_cost": 2996.912924744617, "drugs_acting_on_benzodiazepine_receptors_cost": 2993.142806352308, "drugs_affecting_the_renin_angiotensin_system_cost": 20255.500615282508, "drugs_used_in_parkinsonism_and_related_disorders_cost": 9812.457888596877}

Теперь мне нужны SUM значения JSONB по всей таблице, но я не знаю, как это сделать. В идеале я бы вернулся в словарь, где ключи такие же, как указано выше, а значения - это суммированные значения.

Я могу сделать следующее для SUM одного поля JSONB явно:

    SELECT date, SUM(total_list_size) as total_list_size, 
    SUM((star_pu->>'oral_antibacterials_item')::float) AS star_pu_oral_antibac_items
    FROM mytable GROUP BY date ORDER BY date

Но как мне рассчитать суммы для всех атрибутов в поле JSONB - и, желательно, вернуть все поле в виде словаря? В идеале я бы получил что-то вроде:

star_pu │ {"statins_cost": very-large-number, "hypnotics_adq": very-large-number, ...

Думаю, я могу получить каждое поле вручную, явно суммируя каждый ключ, но вся причина, по которой у меня есть поле JSONB, заключается в том, что существует много ключей, и они могут измениться.

Можно с уверенностью предположить, что поле JSONB содержит только ключи и значения, т.е. имеет глубину 1.


postgresql json jsonb postgresql-9.4
person Richard    schedule 01.02.2016    source источник

Ответы (3)


arrow_upward
10
arrow_downward

Запрос должен выполнить свою работу:

select date, json_object_agg(key, val)
from (
    select date, key, sum(value::numeric) val
    from mytable t, jsonb_each_text(star_pu)
    group by date, key
    ) s
group by date;

Полученные значения json будут отсортированы в алфавитном порядке по ключам (побочный эффект json_object_agg ()). Не знаю, имеет ли это значение.

person klin    schedule 01.02.2016
comment
Я только что разместил связанный вопрос: stackoverflow.com/questions/35130870/ - person Richard; 01.02.2016

arrow_upward
5
arrow_downward

Я написал расширение Postgres, которое делает именно это. После того, как вы его установили, вы могли:

SELECT jsonb_deep_sum(star_pu) FROM mytable;

Контрольные показатели находятся в 4 с для 2 миллионов строк, ответ @ klin занимает 11 с.

person Gabriel Furstenheim    schedule 10.06.2017

arrow_upward
1
arrow_downward

Возможно, есть способ получше, но, по крайней мере, он работает:

WITH
  keys AS (SELECT DISTINCT jsonb_object_keys(star_pu) AS key FROM mytable),
  sums AS (SELECT key, sum((star_pu->>key)::float) AS total FROM keys, mytable GROUP BY key)
  SELECT json_object(array_agg(key), array_agg(total::text))::jsonb FROM sums

По сути, он разбивает jsonbs на строки, получает от них имена, суммирует их, объединяет в массивы и создает структуру jsonb. К сожалению, функции jsonb_object() нет, поэтому мы должны преобразовать ее в json, а затем преобразовать в jsonb.

person Sami Kuhmonen    schedule 01.02.2016
comment
jsonb_object был добавлен в 9.5 - person Schwern; 11.09.2019