Postgresql nested row_to_json not working
What about:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select row_to_json(row(array_agg(tweets))) from tweets;


This is actually one of the really cool things about PostgreSQL, arrays, and complex types.  One can aggregate them and then convert.  You should get a structure which is effectively:

tweets[] (i.e. an array of tweet tuples) which then get should converted to JSON properly).

Now, if you need a more complex structure than this, you probably want to define types separately.  Something like:

CREATE TYPE tweet_set AS (
    set_id int,
    tweets tweet[],
    generated_at timestamp, 
    ....
);


And then come up with a query to generate that, and then convert it to JSON.

One of the basic issues is that since row_to_json expects a record type, you have to pass in a record, not an anyarray.  You could also do:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select array_to_json(array_agg(tweets)) from tweets;