Postgresql – how to limit amount of rows in jsonb_agg & jsonb_build_object

postgresqlquery

I have this query:

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

and I get the following result:

enter image description here

However I want to limit the amount of objects in the array to a certain number like 3.
How can I do this? Adding LIMIT 3 is not working.

Best Answer

LIMIT 3 will work just fine if you add it in the right fashion, that is before the rows are aggregated:

SELECT jsonb_agg(jsonb_build_object('me_id', me_id))
FROM (SELECT me_id FROM message LIMIT 3) AS subquery;