I prefer to aggregate sub query results as a JSON array in PostgreSQL.
This is an example.
SELECT
a.name,
(
SELECT json_agg(json_build_object('col1', b.col1)) AS data
FROM table_b AS b WHERE b.year = a.year
) AS data
FROM
table_a AS a
How can I do the same thing in Redshift?
As I expect you know redshift is based on Postgres 8 and does not have these functions. I can think of 2 ways you may want to proceed. 1) write a python app to connect to Redshift natively and use the python libraries to build the JSON output you desire. This could be the right way to go if the JSONs are complex and/or if the reader of the JSON is some automation. You should be able to make a stored procedure out of this python. Or 2) use list_agg() to generate the JSON string your are looking for. This route is fairly straight forward and should work ok for simple JSON constructs. You can also code this up in a stored procedure if you like.
#2 looks something like (untested):
SELECT '[' || list_agg('{"col1": "' || col1 || '"}', ', ') || ']' FROM ...