PostgreSQL: joining on jsonb array Using this contrived example structure:

Base table data

{
  "uid": "b12345",
  "nested": ["n12345", "n34567"]
}


Nested table data

[
  { "uid": "n12345", "message": "Hello world" },
  { "uid": "n34567", "message": "Hello world" }
]


I'm trying to join the tables on the nested array such that each uid is replaced with its corresponding record:

{
  "uid": "b12345",
  "nested": [
    { "uid": "n12345", "message": "Hello world" },
    { "uid": "n34567", "message": "Hello world" }
  ]
}


The solution in this post looks very close to what I need, but it seems like the main difference/blocker is that the nested array here is initially flat.

Here is the SQL I've been using to test it, including a query closely modeled on the above post. I'll appreciate any help!
This seems to do what you want:

select jsonb_build_object('uid', b.uid, 'nested', jsonb_agg(to_jsonb(m)))
from base b
  join lateral (
    select n.uid, n.message
    from nested n
      join jsonb_array_elements_text(b.nested) as x(bid) on x.bid = n.uid
  ) m on true
group by b.uid  
order by b.uid;  


Online example: https://rextester.com/ASA37564