jsonb_array_elements() in the select seems to turn a left join into an inner join

PostgreSQL 9.6.

create table jon.vins (vin citext primary key);
insert into jon.vins values
('3GNAXUEV1KL221776'),
('3GNAXHEV2KS548975');

CREATE TABLE jon.describe_vehicle (
  vin citext primary key,
  response jsonb);

jon.describe_vehicle contains data for only 1 vin, 3GNAXHEV2KS548975;

This query:

select a.vin,
      b.response->'attributes'->>'bestMakeName' as make
    from jon.vins a
    left join jon.describe_vehicle b on a.vin = b.vin;

Returns what I expect, one row for each vin in jon.vins:

vin        |   make    
-------------------+-----------
 3GNAXUEV1KL221776 | 
 3GNAXHEV2KS548975 | Chevrolet
(2 rows)

But this query:

select a.vin,
      jsonb_array_elements(b.response->'style')->'attributes'->>'name' as style
    from jon.vins a
    left join jon.describe_vehicle b on a.vin = b.vin;

Returns:

vin        |      style       
-------------------+------------------
 3GNAXHEV2KS548975 | FWD 4dr LS w/1LS
(1 row)

It is as if the jsonb_array_elements in the select turns the left join into an inner join.

I expected to see a row for vin 3GNAXUEV1KL221776 with a null value for style.

What am I doing wrong?

Set returning functions should be put into the from clause. Putting them into the SELECT clause is allowed, but can result in strange result (as you have noticed).

The clean approach would be:

select a.vin, r.data ->'attributes'->>'name' as style
from vins a
  left join describe_vehicle b on a.vin = b.vin
  left join jsonb_array_elements(b.response->'style') as r(data) on true

Online example: https://rextester.com/RGY32895

StackExchange Database Administrators Q#222900, answer score: 8