limit amount of objects in array of postgresql jsonb_agg

Question

I have this query:

SELECT jsonb_agg(jsonb_build_object('me_id', me_id)) from message

and got the following result:

[{"me_id": 2064}, {"me_id": 2065}, {"me_id": 2066}, {"me_id": 2067}, {"me_id": 2068}, {"me_id": 2069}, {"me_id": 2070}, {"me_id": 2071}]

so far so good. However I want to LIMIT the amount of objects in the array. For instance to 3. But adding LIMIT 3 is not working.

How can I limit the amount of objects within the array?

EDIT: @GMB's answer solved it. Thank you!

GMB · Accepted Answer · 2020-12-23 00:32:53Z

3

You would need a subquery:

select jsonb_agg(jsonb_build_object('me_id', me_id)) 
from (select * from message order by me_id limit 3) t

answered Dec 23, 2020 at 0:32

GMB

224k25 gold badges103 silver badges151 bronze badges

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

5 Comments

André Over a year ago

Hey that almost did the trick... However, when I want to do a LEFT JOIN now, I get "me_id" is ambiguous: --> SELECT jsonb_agg(jsonb_build_object('id', me_id)) from (select * FROM message LEFT JOIN message_photo ON (message_photo.me_id = message.me_id) order by message.me_id DESC limit 3) t

GMB Over a year ago

There are two columns called me_id in the resultset of the subquery, so that's ambiguous. Use something like select message.m_id from .... Or you can use the USING syntax in the join to eliminate the ambiguity.

André Over a year ago

can you maybe tell me the exact query please? I am a bit overwhelmed by it. I tried by using SELECT message.m_id but then I get this error: missing FROM-clause entry for table "message"

GMB Over a year ago

@André: in your code, just replace ON (message_photo.me_id = message.me_id) with USING (me_id). Or: replace select * with select message.m_id.

André Over a year ago

Thank you very much that solved it !! You were really helpful, thank you very much.

Collectives™ on Stack Overflow

limit amount of objects in array of postgresql jsonb_agg

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related