0

I am trying to achieve following (Result Required) output from POSTGRES jsonb columns, but not getting desired result using "jsonb_agg" function. I went through this postgres document https://www.postgresql.org/docs/12/functions-json.html, but no luck here.
Also suggest good resource for json formatting related stuff for postgres.

City JColA JColB
NY [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":20.12,"full_name":null},{"id":"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount":11.55,"full_name":null},{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":5.45,"full_name":null}] [{"key":"key1","value":"1"},{"key":"key2","value":"2"},{"key":"key3","value":"3"}]
DC [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":1.5,"full_name":null},{"id":"ID3","name":"ID3_NAME","type":"ID3_TYPE","amount":1.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":1,"full_name":null}] [{"key":"key1","value":"1"},{"key":"key1","value":"2"},{"key":"key1","value":"3"}]
DL [{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":1.5,"full_name":null},{"id":"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount":1.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":1,"full_name":null}] [{"key":"key1","value":"2"},{"key":"key2","value":"2"},{"key":"key3","value":"4"}]
NY [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":4.5,"full_name":null},{"id":"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount":2.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":6,"full_name":null}] [{"key":"key4","value":"2"},{"key":"key2","value":"5"},{"key":"key2","value":"4"}]
DC [{"id":"ID3","name":"ID3_NAME","type":"ID3_TYPE","amount":2.5,"full_name":null},{"id":"ID3","name":"ID3_NAME","type":"ID3_TYPE","amount":2.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":2,"full_name":null}] [{"key":"key1","value":"2"},{"key":"key2","value":"2"},{"key":"key3","value":"4"}]

Required Result

City AggJSonColA AggJsonColB
NY [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":30.07,"full_name":null},{"id":"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount":13.75,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":6,"full_name":null}] [{"key":"key1","value":"1"},{"key":"key2","value":"11"},{"key":"key3","value":"3"}, {"key":"key4","value":"2"}]
DC [{"id":"ID1","name":"ID1_NAME","type":"ID1_TYPE","amount":1.5,"full_name":null},{"id":"ID3","name":"ID3_NAME","type":"ID3_TYPE","amount":5.9,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":3,"full_name":null}] [{"key":"key1","value":"8"},{"key":"key2","value":"2"},{"key":"key3","value":"4"}]
DL [{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":1.5,"full_name":null},{"id":"ID2","name":"ID2_NAME","type":"ID2_TYPE","amount":1.2,"full_name":null},{"id":"ID4","name":"ID4_NAME","type":"ID4_TYPE","amount":1,"full_name":null}] [{"key":"key1","value":"2"},{"key":"key2","value":"2"},{"key":"key3","value":"4"}]

2 Answers 2

1

You can consecutively use JSONB_POPULATE_RECORDSET() along with creating types for each JSONB columns and then JSONB_BUILD_OBJECT() to combine the key value pairs while summing up the numeric values such as

WITH t0 AS
(
 SELECT city, 
        (JSONB_POPULATE_RECORDSET(NULL::jstA,JColA)).*,
        (JSONB_POPULATE_RECORDSET(NULL::jstB,JColB)).*
   FROM t
), tA AS
(
 SELECT city,
        JSONB_BUILD_OBJECT('id',id,'name',name,'type',type,'amount',SUM(amount),'full_name',full_name) AS jsA
   FROM t0
  GROUP BY city, id, name, type, full_name
  ORDER BY id
), tB AS
(
 SELECT city,
        JSONB_BUILD_OBJECT('key',key,'value',SUM(value)) AS jsB
   FROM t0
  GROUP BY city, key
  ORDER BY key
)
SELECT *
  FROM (SELECT city, JSONB_PRETTY(JSONB_AGG(jsA)) AS AggJsonColA FROM tA GROUP BY city) AS tA
  JOIN (SELECT city, JSONB_PRETTY(JSONB_AGG(jsB)) AS AggJsonColB FROM tB GROUP BY city) AS tB
    ON tB.city=tA.city

Demo

Sign up to request clarification or add additional context in comments.

Comments

1

You'll have to aggregate jColA and jColB values in different subselects.

You have to unnest your jColA and jColB arrays (using json_array_elements) and then expand the result into fields (using json_to_recordset or simple ->> operator).

After you have plain dataset, you can sum amout column from jColA grouping by other jColA attributes and value JColB grouping by key.

After you that you have to join the results of subqueries by the city.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.