title: How to join nested jsonb array elements?
body: <p>My question is somewhat similar to this one:<br>
  <a href="https://stackoverflow.com/questions/40413861/how-to-join-jsonb-array-elements-in-postgres">How to join jsonb array elements in Postgres?</a></p>
  
  <p>But I have some nested arrays I need to fill in. To keep it simple I only have 1 table:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>See: <a href="http://sqlfiddle.com/#!17/7c7ef/12/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/7c7ef/12/0</a></p>
  
  <p>I am trying to add simply the name to each of the coworkers and employees so that bob would look like:</p>
  
  <pre><code>[
    {
      "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"}]
    }
  ]
  </code></pre>
  
  <p>So far, I have:</p>
  
  <pre><code>SELECT c.person person
  FROM tester
  LEFT JOIN LATERAL(
      SELECT jsonb_agg(
          jsonb_build_object(
              'employees', c.wrk-&gt;'employees',
              'coworkers', c.wrk-&gt;'coworkers'
          )
      ) AS person
      FROM jsonb_array_elements(tester.d) AS c(wrk)
  ) c ON true
  </code></pre>
  
  <p>Which returns everything but the names:</p>
  
  <pre><code>[{"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)
  </code></pre>
  
  <p>Please take note of the list of objects: they are separate objects and not just one big object. </p>
  
  <p>The "(null)" s/b a blank array "[]".</p>