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.

What's wrong with the subquery (which is actually a derived table). If you need to change every element of an array, then the only choice you have is to unnest the array, change each element and aggregate back - exactly what you are doing. And this shows once again why JSON isn't always a good idea. In a a properly normalized model this wouldn't be necessary to begin with — user330315
– user330315, Commented Feb 4, 2021 at 13:40
Thanks for the comment and I agree there's nothing wrong with the subquery. This is more for my own personal learning about JSON processing in postgres rather than a specific application. — Adam
– Adam, Commented Feb 4, 2021 at 13:51

user330315 · Accepted Answer · 2021-02-04 14:11:52Z

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

answered Feb 4, 2021 at 14:11

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building a jsonb object without using subquery in postgresql 12

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related