2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Thomas Chen
  • 37
  • 1
  • 7

1 Answers1

2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hitting a ERROR: column "resource" specified in USING clause does not exist in left table. – Thomas Chen Aug 02 '18 at 16:52
  • @ThomasChen: Forgot that the column name defaults to `value` for the function `jsonb_array_elements_text()`. Added a column alias and it should work now. – Erwin Brandstetter Aug 02 '18 at 17:24
  • Followup: Having an edge case issue where if resources is empty [] then the join does not include that row from table 1. Is there a way to include table 1 rows with empty resources and join them to a blank row of table 2 on top of what it's doing now? – Thomas Chen Aug 02 '18 at 20:54
  • @ThomasChen. There is. `LEFT JOIN .. ON true`. Follow the first link in my answer for a detailed assessment. [The included fiddle should give a quick overview](https://dbfiddle.uk/?rdbms=postgres_11&fiddle=588a9cba87833e8969e35a988f4cbb4f). I added a variant above to illustrate. – Erwin Brandstetter Aug 02 '18 at 20:58