如何在多个行满足需求时通过联接在PostgreSQL中进行选择？

Question

如何进行选择以在一个单元格中获取JSON数组时进行INNER JOIN加入时有多个值？

前表：

T1：

id | name 
1 Tom 
2 Dom 

T2：

user_id | product 
1   Milk 
2   Cookies 
2   Banana 

当然，我做SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.user_id。 但后来我得到：

id | Name | product 
1 Tom  Milk 
2 Dom  Cookies 
2 Dom  Banana 

但我想：

id | Name | product 
1 Tom  [{"product":"Milk}] 
2 Dom  [{"product":"Cookies"}, {"product":"Banana"}] 

如果我做一些与agg功能，然后我需要把一切在GROUP BY，我在那里至少有10参数。整个查询需要5分钟以上。

我的T1约为4000行左右，T2约300000行，每个行与T1中的某一行相关。

有没有更好的方法？

Answer 1

使用LATERAL你可以解决它下面给出的例子：

-- The query 
SELECT * 
FROM table1 t1, 
LATERAL ( SELECT jsonb_agg(
         jsonb_build_object('product', product) 
        ) 
      FROM table2 
      WHERE user_id = t1.id 
     ) t2(product); 

-- Result 
id | name |      product 
----+------+------------------------------------------------- 
    1 | Tom | [{"product": "Milk"}] 
    2 | Dom | [{"product": "Cookies"}, {"product": "Banana"}] 
(2 rows) 


-- Test data 
CREATE TABLE IF NOT EXISTS table1 (
    id  int, 
    "name" text 
); 

INSERT INTO table1 
VALUES (1, 'Tom'), 
     (2, 'Dom'); 

CREATE TABLE IF NOT EXISTS table2 (
    user_id  int, 
    product  text 
); 

INSERT INTO table2 
VALUES (1, 'Milk'), 
     (2, 'Cookies'), 
     (2, 'Banana');