How to join nested jsonb array elements?

Question

My question is somewhat similar to this one:
How to join jsonb array elements in Postgres?

But I have some nested arrays I need to fill in. To keep it simple I only have 1 table:

CREATE table tester(
  id int,
  name text,
  d jsonb  
)

INSERT INTO tester(id, name, d) VALUES
  ('1', 'bob',    '[
                     {
                       "employees": [{"id":2},{"id":3},{"id":4}],
                       "coworkers": [{"id":5},{"id":6}]
                     },
                     {
                       "employees": [{"id":3},{"id":4}],
                       "coworkers": [{"id":5}]
                     }
                   ]'::jsonb),
   ('2', 'barb',    '[
                     {
                       "employees": [{"id":3}],
                       "coworkers": []
                     },
                     {
                       "employees": [{"id":3},{"id":4}],
                       "coworkers": [{"id":5, "id":3}]
                     }
                   ]'::jsonb),

   ('3', 'ann',    '[]'::jsonb),
   ('4', 'jeff',   '[]'::jsonb),
   ('5', 'rachel', '[]'::jsonb),
   ('6', 'ryan',   '[]'::jsonb);

See: http://sqlfiddle.com/#!17/7c7ef/12/0

I am trying to add simply the name to each of the coworkers and employees so that bob would look like:

[
  {
    "employees": [{"id":2, "name":"barb"},{"id":3, "name":"ann"},{"id":4, "jeff"}],
    "coworkers": [{"id":5, "name":"rachel"},{"id":6, "name":"ryan"}]
  },
  {
    "employees": [{"id":3, "name":"ann"},{"id":4, "name":"jeff"}],
    "coworkers": [{"id":5, "name":"rachel"}]
  }
]

So far, I have:

SELECT c.person person
FROM tester
LEFT JOIN LATERAL(
    SELECT jsonb_agg(
        jsonb_build_object(
            'employees', c.wrk->'employees',
            'coworkers', c.wrk->'coworkers'
        )
    ) AS person
    FROM jsonb_array_elements(tester.d) AS c(wrk)
) c ON true

Which returns everything but the names:

[{"coworkers": [{"id": 5}, {"id": 6}], "employees": [{"id": 2}, {"id": 3}, {"id": 4}]}, {"coworkers": [{"id": 5}], "employees": [{"id": 3}, {"id": 4}]}]
[{"coworkers": [], "employees": [{"id": 3}]}, {"coworkers": [{"id": 3}], "employees": [{"id": 3}, {"id": 4}]}]
(null)
(null)
(null)
(null)

Please take note of the list of objects: they are separate objects and not just one big object.

The "(null)" s/b a blank array "[]".

Answer 1

Assuming that tester.id is the PK, to simplify the aggregation:

SELECT t.id, t.name, COALESCE(t1.d, t.d)
FROM   tester t
LEFT   JOIN LATERAL (
   SELECT jsonb_agg(jsonb_build_object('coworkers', COALESCE(c.coworkers, jsonb '[]'))
                 || jsonb_build_object('employees', COALESCE(e.employees, jsonb '[]'))) AS d
   FROM   jsonb_array_elements(t.d) AS d1(p)
   CROSS  JOIN LATERAL (
      SELECT jsonb_agg(p.id || jsonb_build_object('name', n.name)) AS coworkers
      FROM   jsonb_array_elements(d1.p ->'coworkers') AS p(id)
      LEFT   JOIN tester n ON n.id = (p.id->>'id')::int
      ) c
   CROSS  JOIN LATERAL (
      SELECT jsonb_agg(p.id || jsonb_build_object('name', n.name)) AS employees
      FROM   jsonb_array_elements(d1.p ->'employees') AS p(id)
      LEFT   JOIN tester n ON n.id = (p.id->>'id')::int
      ) e
   GROUP  BY t.id
   ) t1 ON t.d <> '[]';

SQL Fiddle.

Explanation is much like in my old answer you referenced:

• How to join jsonb array elements in Postgres?

One special difficulty is to retain the empty JSON array '[]' where the aggregation would returns NULL values, I solved this with the strategic use of COALESCE().

Another one is that you want to keep nested arrays apart. Solved that with aggregating unnested arrays right back into JSON arrays, in two separate LATERAL joins for coworkers and employees.

---

Note the trap in your data for barb: "coworkers": [{"id":5, "id":3}]

SELECT jsonb '[{"id":5, "id":3}]' results in '[{"id": 3}]'. Maybe you meant to write '[{"id":5}, {"id":3}]'?