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!

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. — Morris de Oryx
– Morris de Oryx, Commented Oct 31, 2019 at 6:43

user1822 · Accepted Answer · 2019-10-30 18:26:29Z

3

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

answered Oct 30, 2019 at 18:26

user1822

Add a comment |

Stack Exchange Network

PostgreSQL: joining on jsonb array

1 Answer 1

Hot Network Questions

PostgreSQL: joining on jsonb array

1 Answer 1

Related

Hot Network Questions