Postgresql left outer join on json array

Question

I'm wondering if it's possible to do a left outer join between a json_array_elements of a table column and another table? Something like the following, but this doesn't work.

SELECT *
FROM foo,
    json_array_elements (foo.bars :: json) foo_bars
LEFT OUTER JOIN bar ON (foo_bars ->> 'id') :: BIGINT = bar.ID;

The table structure is like the following

FOO
 ------------------------------------------
|  ID  | NAME |            BARS            |
|------------------------------------------|
|  1   | FOO1 | [{ "id" : 1}, { "id" : 2 }]|
|------------------------------------------|
|  2   | FOO1 |             []             |
 ------------------------------------------

BAR
 -------------
|  ID  | NAME |
|-------------|
|  1   | BAR1 |
|-------------|
|  2   | BAR2 |
 -------------

I would expect the output of the query to be

 --------------------------------------------------------
|  ID  | NAME |            BARS            |  ID  | NAME |
|------------------------------------------|-------------|
|  1   | FOO1 | [{ "id" : 1}, { "id" : 2 }]|  1   | BAR1 |
|------------------------------------------|-------------|
|  1   | FOO1 | [{ "id" : 1}, { "id" : 2 }]|  2   | BAR2 |
|------------------------------------------|-------------|
|  2   | FOO1 |             []             | null | null |
 --------------------------------------------------------

Solution

To answer your question: Yes it is possible and your query does exactly that. We can prove it by introducing a third row in foo table: http://sqlfiddle.com/#!15/06dfe/2

Your problem is not with LEFT JOIN to json_array_elements but with implicit lateral cross join. Your query is equivalent to:

SELECT *
FROM foo 
  CROSS JOIN LATERAL json_array_elements (foo.bars :: json) foo_bars 
  LEFT OUTER JOIN bar ON (foo_bars ->> 'id') :: BIGINT = bar.ID;

http://sqlfiddle.com/#!15/06dfe/5

What you want is a lateral left join between foo and json_array_elements:

SELECT *
FROM foo LEFT JOIN LATERAL
    json_array_elements (foo.bars :: json) foo_bars ON true
LEFT OUTER JOIN bar ON (foo_bars ->> 'id') :: BIGINT = bar.ID;

http://sqlfiddle.com/#!15/06dfe/6

Comments

[binarymelon]

This works great. Even works with null json as well. I'd never heard of SQL Fiddle before either. I wish I could give you 100 upvotes for that one.

[鄭元傑]

I remove the LATERAL and the result is the same.

[Jakub Kania]

@鄭元傑 it's implicit probably, it was years ago and I don't understand it anymore.

[鄭元傑]

@JakubKania I am still trying to understand LATERAL. OTZ

[Goofyahead]

popsql.com/learn-sql/postgresql/how-to-use-lateral-joins