Search code examples
postgresqljsonb

PostGres joins Using JSONB

I have two tables which look like such

Organizations
Id (primary_key. big int)
Name (text)

CustomInformations
Id (primary_key. big int)
ConnectedIdentifiers (JSONB)
Info (text)

CustomInformations's ConnectedIdentifiers column contains a JSONB structure which looks like

{ 'organizations': [1,2,3] }

This means that there's an array of organizations with those ids 1, 2, 3, which are related to that particular CustomInformation

I'm trying to do a JOIN where given a CustomInformation Id will also get me all the Organizations names

I tried this after looking at some examples:

SELECT * FROM CustomInformations ci
INNER JOIN Organizations o on jsonb_array_elements(ci.ConnectedIdentifiers->'19') = o.id
WHERE
   ci.id = 5

I got an error No operator matches the given name and argument type(s). You might need to add explicit type casts.

Is this the right approach? And if so what is wrong with my syntax?

Thanks

Solution

  • You cannot use jsonb_array_elements() in this way because the function returns set of rows. It should be placed in a lateral join instead. Use jsonb_array_elements_text() to get array elements as text and cast these elements to bigint:

    select ci.*, o.*
from custominfo ci
-- lateral join 
cross join jsonb_array_elements_text(ci.connectedidentifiers->'organizations') ar(elem)
join organizations o 
on elem::bigint = o.id
where ci.id = 5