Search code examples
sqljsonpostgresqljsonbpostgresql-9.6

How to JOIN a JSONB column with an array by a text column?

Using PostgreSQL 9.6+

Two tables (simplified to only the columns that matter with example data):

Table 1:

-------------------------------------------------------
key (PK) [Text]| resources [JSONB]
-------------------------------------------------------
asdfaewdfas    | [i0c1d1233s49f3fce, z0k1d9921s49f3glk]

Table 2:

-------------------------------------------------------
resource (PK) [Text]| data [JSONB]
-------------------------------------------------------
i0c1d1233s49f3fce   | {large json of data}
z0k1d9921s49f3glk   | {large json of data}

Trying to access the data column(s) of Table 2 from the resources column of Table 1.

Solution

  • Unnest the JSON array and join to the second table. Like:

    SELECT t1.*, t2.data -- or just the bits you need
FROM   table1 t1, jsonb_array_elements_text(t1.resources) r(resource)
JOIN   table2 t2 USING (resource)
WHERE  t1.key = ?

    Or, to preserve all rows in table1 with empty / null / unmatched resources:

    SELECT t1.*, t2.data -- or just the bits you need
FROM   table1 t1
LEFT   JOIN LATERAL jsonb_array_elements_text(t1.resources) r(resource) ON true
LEFT   JOIN table2 t2 USING (resource)
WHERE  t1.key = ?

    About jsonb_array_elements_text():

    There is an implicit LATERAL join in the first query. See:

    Consider a normalized DB design with a junction table with one row per linked resource instead of the column table1.resources, implementing the m:n relation properly. This way you can enforce referential integrity, data integrity etc. with relational features. And queries become simpler. jsonb for everything is simple at first. But if you work a lot with nested data, this may turn around on you.