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 "[]".

With additional service function it could be simpler: http://dbfiddle.uk/?rdbms=postgres_10&fiddle=ccea0da81d9caeaa55d36519811e6755 — Abelisto, Nov 14 '17 at 01:09
I think you'll need two lateral joins for each of coworkers & employees — Haleemur Ali, Nov 14 '17 at 01:56

Haleemur Ali · Answer 1 · 2017-11-14T05:20:15.003

Using two lateral joins we can create the arrays for coworkers and employees, where we join to the tester table within the lateral query to get the name, then construct the jsonb object & aggregate to get the transformed array.

The resulting query is hairy, though not overly complicated.

SELECT 
    "name"
  , CASE 
      WHEN d = '[]'::jsonb THEN NULL 
      ELSE coworker.people || employee.people 
    END relationships
FROM tester
, LATERAL (
    SELECT 
        jsonb_build_object(
             'coworkers'
           , JSON_AGG(json_build_object('id', id, 'name', "name"))
        ) people
    FROM (SELECT DISTINCT 
              (jsonb_array_elements(el->'coworkers')->>'id')::int id
          FROM jsonb_array_elements(d) el) coworker
    NATURAL JOIN tester
    ) coworker
, LATERAL (
    SELECT 
        jsonb_build_object(
           'employees'
          , JSON_AGG(json_build_object('id', id, 'name', "name"))) people
    FROM (SELECT DISTINCT 
              (jsonb_array_elements(el->'employees')->>'id')::int id
          FROM jsonb_array_elements(d) el) employee
    NATURAL JOIN tester
    ) employee

alternate solution for list of objects:

WITH people_separated AS (
  SELECT 
    "name"
  , coworkers
  , employees
  FROM tester
  , LATERAL (
    SELECT
      k->'coworkers' coworkers
    , k->'employees' employees 
    FROM jsonb_array_elements(d) k
  ) split
) 
, people_relationships AS (
SELECT 
  name
, JSON_AGG(
    CASE WHEN e.people IS NULL THEN '{}'::jsonb ELSE jsonb_build_object('employees', e.people) END
    || 
    CASE WHEN c.people IS NULL THEN '{}'::jsonb ELSE jsonb_build_object('coworkers', c.people) END
  ) relationships
FROM people_separated
, LATERAL (
    SELECT JSON_AGG(
        c || jsonb_build_object(
                  'name'
                , (SELECT name FROM tester WHERE id = (c->>'id')::int)
             )
    ) people 
    FROM jsonb_array_elements(coworkers) c) c
, LATERAL (
    SELECT JSON_AGG(
        e || jsonb_build_object(
                  'name'
                , (SELECT name FROM tester WHERE id = (e->>'id')::int)
             )
    ) people
    FROM jsonb_array_elements(employees) e) e
GROUP BY 1
)
SELECT name, relationships FROM tester LEFT JOIN people_relationships USING (name)

That is kind of there but is giving a single object. I need a list of objects. http://sqlfiddle.com/#!17/7c7ef/15/0 — , Nov 14 '17 at 04:46
answer updated, same principle, just aggregations applied at a different level — Haleemur Ali, Nov 14 '17 at 05:19
That's almost it but it is leaving out one of barb's coworkers: http://sqlfiddle.com/#!17/7c7ef/16/0 — , Nov 14 '17 at 20:23
Turns out I had a mistake in my question re: barb's coworkers. My bad. Bounty on another of your answers incoming. thanks. — , Nov 18 '17 at 18:06
I realized after I accepted Erwin's answer that the fault was mine. Thank you again! — , Nov 20 '17 at 23:26

Erwin Brandstetter · Accepted Answer · 2017-11-18T17:42:32.427

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}]'?

How to join nested jsonb array elements?

2 Answers2

Linked