PostgreSQL在JSON数组上左外连接 [英] Postgresql left outer join on json array

问题描述

我想知道是否有可能在一个表列的json_array_elements和另一个表之间进行左外部联接?类似于以下内容，但这不起作用.

I'm wondering if it's possible to do a left outer join between a json_array_elements of a table column and another table? Something like the following, but this doesn't work.

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

推荐答案

要回答您的问题:是的，这是可能的，而您的查询正是这样做的. 我们可以通过在foo表中引入第三行来证明这一点: http://sqlfiddle.com/#!15/06dfe/2

To answer your question: Yes it is possible and your query does exactly that. We can prove it by introducing a third row in foo table: http://sqlfiddle.com/#!15/06dfe/2

您的问题不是LEFT JOIN到json_array_elements，而是隐式横向交叉连接.您的查询等同于:

Your problem is not with LEFT JOIN to json_array_elements but with implicit lateral cross join. Your query is equivalent to:

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之间的横向左连接:

What you want is a lateral left join between foo and 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

这篇关于PostgreSQL在JSON数组上左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！