How to add key/value to element in array of array (jsonb type) in plain sql (encountered ERROR: aggregate function calls cannot be nested)

Question

dbfiddle here: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=5924abbdad955e159de7f3571ebbac5a

Say I've a table

CREATE TABLE yewu (
  id serial PRIMARY KEY,
  org varchar(50),
  data jsonb
);

I want to update table

id  org data
1   OA  [{"profiles": [{"id": 1}, {"id": 2}]}, {"profiles": [{"id": 3}, {"id": 4}]}]
2   OB  [{"profiles": [{"id": 1}, {"id": 2}]}, {"profiles": [{"id": 3}, {"id": 4}]}]

to

id  org data
1   OA  [{"profiles": [{"id": 1,"org":"OA"}, {"id": 2,"org":"OA"}]}, {"profiles": [{"id": 3,"org":"OA"}, {"id": 4,"org":"OA"}]}]
2   OB  [{"profiles": [{"id": 1,"org":"OB"}, {"id": 2,"org":"OB"}]}, {"profiles": [{"id": 3,"org":"OB"}, {"id": 4,"org":"OB"}]}]

this is what I try:

UPDATE
  yewu
SET
  data = (
    SELECT
      jsonb_agg((
        SELECT
          jsonb_set(oc, '{profiles}', jsonb_agg((
              SELECT
                jsonb_set(p, '{org}', yewu.org::jsonb)
              FROM jsonb_array_elements(oc -> 'profiles') p)))))
    FROM
      jsonb_array_elements(data) oc)
RETURNING
  id;

and error:

ERROR:  aggregate function calls cannot be nested
LINE 8:           jsonb_set(oc, '{profiles}', jsonb_agg((
                                              ^

Ramin Faracov · Accepted Answer · 2022-04-07 23:30:33Z

1

Sample Query:

select 
    t2.id, 
    t2.org,  
    jsonb_agg(t2.p1) as "data"
from (
    select 
        t1.id, 
        t1.org, 
        t1.profiles, 
        jsonb_build_object('profiles', jsonb_agg(t1.p1)) as p1 
    from (
        select 
            id, 
            org, 
            jsonb_array_elements("data")->'profiles' as "profiles", 
            jsonb_array_elements(jsonb_array_elements("data")->'profiles') || jsonb_build_object('org', org) as p1
        from yewu
    ) t1 
    group by t1.id, t1.org, t1.profiles
) t2 
group by t2.id, t2.org 

------------------------- RETURN -------------------------
id      org     data
--------------------------------------------------------------------------------------------------------------------------------------------------
1       OA      [{"profiles": [{"id": 1, "org": "OA"}, {"id": 2, "org": "OA"}]}, {"profiles": [{"id": 3, "org": "OA"}, {"id": 4, "org": "OA"}]}]
2       OB      [{"profiles": [{"id": 1, "org": "OB"}, {"id": 2, "org": "OB"}]}, {"profiles": [{"id": 3, "org": "OB"}, {"id": 4, "org": "OB"}]}]

answered Apr 7, 2022 at 23:30

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

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

1 Comment

tcpiper Over a year ago

wow, learned a lot. jsonb_array_elements to flatten json (like a for loop), jsonb_agg and group by to nest it back. Thanks.

Collectives™ on Stack Overflow

How to add key/value to element in array of array (jsonb type) in plain sql (encountered ERROR: aggregate function calls cannot be nested)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related