主页 / dba / 问题 / 222900
Accepted
jon
Asked: 2018-11-20 08:00:29 +0800 CST

选择中的 jsonb_array_elements() 似乎将左连接变为内连接

  • 772

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仅包含 1 vin、3GNAXHEV2KS548975 的数据；

这个查询：

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

返回我所期望的，每个 vin 在一行jon.vins：

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

但是这个查询：

    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;

回报：

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

就好像jsonb_array_elements在选择中将左连接变成了内连接。

我希望看到一行 vin 3GNAXUEV1KL221776 的样式为空值。

我究竟做错了什么？

postgresql set-returning-functions

2 个回答

  1. Best Answer

    集合返回函数应该放在 from 子句中。将它们放入 SELECT 子句是允许的，但会导致奇怪的结果（如您所见）。

    干净的方法是：

    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

    在线示例：https ://rextester.com/RGY32895

    • 8

  2. 它仍在执行左连接，只是有些行在冒泡到顶层之前被过滤掉了。这就是在查询的选择列表中设置返回函数的工作方式。如果你有一个包含文字 NULL 值的物理行，而不是由左连接生成的 NULL 值，它会做同样的事情。

    解决此问题的一种方法是将 NULL 值替换为具有一个元素的合适的虚拟 JSON 数组：

    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;

    如果您运行的是版本 10 或更高版本，另一种选择是使用始终返回一行的虚拟集合返回函数，并将其添加到您的选择列表中：

    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;

    但这在 9.6 中不起作用。

    • 1

相关问题