PostgreSQL – jsonb_array_elements() Turns Left Join into Inner Join

postgresqlset-returning-functions

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?

Best Answer

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

Related Solutions

Mysql – Error in function creating query

Here's a version that does not give a syntax error:

delimiter !!
drop function _increase_num !! 
CREATE FUNCTION _increase_num() 
returns int 
begin 
    return IF(@counter, @counter:=@counter+1, @counter:=1); 
end !!
delimiter ;

A bit of warning though, whenever counter looses its value it will start from 1. Also, I would not put any money on how well this works when the function is called in parallel from several processes.

Edit: ";" has a special meaning in stored procedure language, it separates statements within the procedure. This is why you normally change the statement terminator to something else:

delimiter @

However, for this trivial procedure, we don't need the begin/end block. That is:

CREATE FUNCTION _increase_num() 
returns int 
    return IF(@counter, @counter:=@counter+1, @counter:=1);

will do.

PostgreSQL – How to Define a GIN Index Across Two Columns/Arrays

Since you want to run the query on metadata->'emails', you need to build the index on that expression:

create index on contacts using gin ((metadata->'emails'));

Alternatively, you could make the index on metadata itself, and re-formulate the query condition like this:

... OR metadata @> '{"emails":[{"email": "my@email.com"}]}'

In either case, you also need an index on the column "email", to satisfy the other branch of the OR condition. Then the two indexes will be combined with a BitmapOr operation.

It is possible you could make one index on both columns using some complicated expression index, but that doesn't really seem worthwhile.

Related Question