3

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?

  • Since Postgres 10, SRF in the SELECT list are treated like LATERAL ROWS FROM ( ... ) - still removing rows without result from the SRF. This is the unfortunate, unintuitive exception to the otherwise great improvements in pg 10. Should also have been changed to LEFT JOIN LATERAL ROWS FROM ( ... ) ON true (doing what a_horse's query does) IMO. Context: stackoverflow.com/a/39864815/939860 – Erwin Brandstetter Nov 20 '18 at 2:52
4

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

1

It is still executing a left join, it is just that some rows are filtered out before they bubble up to the top level. This is how Set Returning Functions in the select list of a query work. It would do the same thing if you had a physical row containing a literal NULL value, rather than a NULL value being generated by a left join.

One way to get around this is to replace NULL values with a suitable dummy JSON array with one element:

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

If you were running version 10 or above, another option would be to use a dummy set returning function which always returns one row, and adding it to your select list:

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

But that doesn't work in 9.6.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.