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!

  • 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. Oct 31, 2019 at 6:43

1 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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.