在JSON数组上使用PostgreSQL左外连接

Question

我想知道是否可以在一个表格列的 json_array_elements 和另一个表之间进行左外连接？就像下面这样，但这是行不通的。

SELECT *
FROM foo,
    json_array_elements (foo.bars :: json) foo_bars
LEFT OUTER JOIN bar ON (foo_bars ->> 'id') :: BIGINT = bar.ID;

表结构如下：

FOO
 ------------------------------------------
|  ID  | NAME |            BARS            |
|------------------------------------------|
|  1   | FOO1 | [{ "id" : 1}, { "id" : 2 }]|
|------------------------------------------|
|  2   | FOO1 |             []             |
 ------------------------------------------

BAR
 -------------
|  ID  | NAME |
|-------------|
|  1   | BAR1 |
|-------------|
|  2   | BAR2 |
 -------------

我会期望查询结果的输出为：

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

- binarymelon

---

“不起作用”并不能很好地描述查询行为与期望行为之间的差异。 - Jakub Kania

Answer 1

回答您的问题：是的，这是可能的，您的查询正是如此。 我们可以通过在foo表中引入第三行来证明: http://sqlfiddle.com/#!15/06dfe/2 您的问题不在于将LEFT JOIN与 json_array_elements 配对，而是存在隐式横向交叉连接。您的查询相当于:

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

你需要的是在foo和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

- Jakub Kania

---

这个很好用。甚至可以处理空的JSON。我以前从未听说过SQL Fiddle。我希望我能为你的贡献点赞100次。 - binarymelon

我移除了 LATERAL，结果是一样的。 - 鄭元傑

@鄭元傑 可能是隱式的，這是幾年前的事情了，我現在已經不理解了。 - Jakub Kania

@JakubKania 我还在努力理解 LATERAL。OTZ - 鄭元傑

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

更新自@Goofyahead评论的链接： https://popsql.com/learn-sql/postgresql/how-to-use-lateral-joins-in-postgresql - dennisjbell