Building a jsonb object without using subquery in postgresql 12

Question

I have a table called users that has a jsonb column called 'history'. This is an array of objects, one of the elements is called uid which is the id of the person visiting the page as follows:

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

I'm running a query that appends the jsonb object with the field uname to make understanding who 'uid' is a bit easier which will produce:

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

I'm currently doing this using the following query (say, where 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

I'm using the subquery to return a table of json objects that's then joined to the user table again to get the username.

My question is: Is there a way of doing this without having the subquery? I've read that lateral joins could be used but all my attempts at this don't seem to work.

Thanks in advance.

question from:https://stackoverflow.com/questions/66046798/building-a-jsonb-object-without-using-subquery-in-postgresql-12

Answer 1

You can move jsonb_array_elements into the FROM clause with an outer join:

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