PostgreSQL: joining on jsonb array

Question

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!

• Morris de Oryx: It's also worth thinking about why you have nested IDs, and if you could design your tables to take advantage of relational features directly. Meaning, without a lot of unseating, etc.

Answer 1

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