在postgresql 12中不使用子查询构建jsonb对象

Question

我有一个名为users的表，它有一个名为'history'的jsonb列。这是一个对象数组，其中一个元素叫做uid，它是访问页面的人的id，如下所示:

[ {"ip":"...","uid":2} , {"ip":"...","uid":4} , ... ]

我正在运行一个查询，将jsonb对象与字段uname附加在一起，以使理解谁'uid'更容易一些，这将产生:

[ {"ip":"...","uid":2,"uname":"bob"} , {"ip":"...","uid":4,"uname":"dave"} , ... ]

我目前正在使用以下查询(例如，uid=2):

SELECT json_agg(history2||jsonb_build_object('uname',uname::text)) FROM 
(SELECT jsonb_array_elements(history) AS history2 FROM users WHERE uid=2) AS table1 
LEFT JOIN users AS table2 ON history2->>'uid'=table2.uid

我使用子查询返回json对象表，然后再次连接到用户表以获取用户名。

我的问题是:有没有一种方法可以做到这一点，而不需要子查询?我读到可以使用横向连接，但我所有的尝试似乎都不起作用。

提前感谢。

Answer 1

您可以使用外部连接将jsonb_array_elements移动到FROM子句中:

SELECT jsonb_agg(h.item||jsonb_build_object('uname', u.uname)) 
FROM users u
LEFT JOIN jsonb_array_elements(u.history) as h(item) on h.item ->> 'uid' = u.uid::text
WHERE u.uid = 2