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

"doesn't work." is not really a good description of query behaviour vs. desired behaviour. — Jakub Kania
– Jakub Kania, Commented Jun 3, 2015 at 20:12

Jakub Kania · Accepted Answer · 2015-06-03 20:37:19Z

23

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

answered Jun 3, 2015 at 20:37

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

binarymelon Over a year ago

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.

鄭元傑 Over a year ago

I remove the LATERAL and the result is the same.

Jakub Kania Over a year ago

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

鄭元傑 Over a year ago

@JakubKania I am still trying to understand LATERAL. OTZ

Goofyahead Over a year ago

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

|

Collectives™ on Stack Overflow

Postgresql left outer join on json array

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related