2

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 am not that good in json data in postgres, so please 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"}]
1
  • 1
    Sample data as CREATE/INSERT statements would help, as would your current attempt Commented Sep 9, 2021 at 14:30

1 Answer 1

3

You need to break out the arrays with jsonb_to_recordset, rebuild the objects using jsonb_build_object, and aggregate them back up with jsonb_agg

SELECT
  A.City,
  A.JColA,
  B.JColB
FROM (
    SELECT
      City,
      jsonb_agg(JColA) AS JColA
    FROM (
        SELECT
          t.City,
          json_build_object(
            'id', id,
            'name', name,
            'type', type,
            'amount', SUM(amount),
            'full_name', full_name
          ) AS JColA
        FROM YourTable t,
        LATERAL jsonb_to_recordset(t.JColA)
          AS arr(id varchar(10), name varchar(100), type varchar(100), amount decimal (18,2), full_name varchar(100))
        GROUP BY
          t.City, arr.id, arr.name, arr.type, arr.full_name
    ) A
    GROUP BY
      City
) A
JOIN (
    SELECT
      City,
      jsonb_agg(JColB) AS JColB
    FROM (
        SELECT
          t.City,
          json_build_object(
            'key', "key",
            'value', SUM(value)
          ) AS JColB
        FROM YourTable t,
        LATERAL jsonb_to_recordset(t.JColB)
          AS arr("key" varchar(10), value int)
        GROUP BY
          t.City, arr."key"
   ) B
    GROUP BY
      City  
) B ON B.City = A.City;

I feel it's easier to requery the original table again, however if you want to avoid that, you could first aggregate all the arrays together by City, break them back out and re-aggregate.

SELECT
  t.City,
  (
    SELECT
      jsonb_agg(JColA)
    FROM (
        SELECT
          json_build_object(
            'id', id,
            'name', name,
            'type', type,
            'amount', SUM(amount),
            'full_name', full_name
          ) AS JColA
        FROM jsonb_array_elements(t.JColA) AS outerArr,
        LATERAL jsonb_to_recordset(outerArr)
          AS arr(id varchar(10), name varchar(100), type varchar(100), amount decimal (18,2), full_name varchar(100))
        GROUP BY
          arr.id, arr.name, arr.type, arr.full_name
    ) A
  ) AS JColA,
  (
    SELECT
      jsonb_agg(JColB)
    FROM (
        SELECT
          json_build_object(
            'key', "key",
            'value', SUM(arr.value)
          ) AS JColB
        FROM jsonb_array_elements(t.JColB) AS outerArr,
        LATERAL jsonb_to_recordset(outerArr)
          AS arr("key" varchar(10), value int)
        GROUP BY
          arr."key"
     ) B
  ) AS JColB
FROM (
    SELECT
      t.City,
      jsonb_agg(JColA) AS JColA,
      jsonb_agg(JColB) AS JColB
    FROM YourTable t
    GROUP BY
      t.City
) t;

db<>fiddle

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

1 Comment

+1 it's quite amazing to me how powerful Postgres is when it comes to slicing and dicing JSON and I also love this answer as an example of why one should AVOID using JSON if it's possible to model data using standard tables and columns - the queries probably become much simpler! (Got here while doing research on JSON anti-patterns)

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.