Postgresql left outer join on json array -

i'm wondering if it's possible left outer join between json_array_elements of table column , table? following, doesn't work.

select * foo,     json_array_elements (foo.bars :: json) foo_bars left outer join bar on (foo_bars ->> 'id') :: bigint = bar.id; 

the table structure following

foo  ------------------------------------------ |  id  | name |            bars            | |------------------------------------------| |  1   | foo1 | [{ "id" : 1}, { "id" : 2 }]| |------------------------------------------| |  2   | foo1 |             []             |  ------------------------------------------  bar  ------------- |  id  | name | |-------------| |  1   | bar1 | |-------------| |  2   | bar2 |  ------------- 

i expect output of query 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 |  -------------------------------------------------------- 

to answer question: yes possible , query that. can prove introducing third row in foo table: http://sqlfiddle.com/#!15/06dfe/2

your problem not left join json_array_elements implicit lateral cross join. query equivalent to:

select * 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 want lateral left join between foo , json_array_elements:

select * 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