CODEWITHSUNDEEP
sqlarraysjsonpostgresqlaggregate-functions
André
André

Reputation: 1268

limit amount of objects in array of postgresql jsonb_agg

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!

Upvotes: 1

Views: 1061

Answers (1)

GMB
GMB

Reputation: 222432

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

Upvotes: 2

Related Questions