Postgres return json with group

Question

Attempting to get a JSON response that is structured like this:

{
  'news_source_1' : [{'headline': 'title'},{'headline': 'title'}],
  'news_source_2' : [{'headline': 'title'},{'headline': 'title'}],
  'news_source_3' : [{'headline': 'title'},{'headline': 'title'}]
}

The query calls a single table grouped by the news_source which is a column in the table.

My code groups by the news source but does not use the news source as a key:

SELECT array_to_json(array_agg(stories)) FROM stories GROUP BY source

Returns:

{
  [{'headline': 'title'},{'headline': 'title'}],
  [{'headline': 'title'},{'headline': 'title'}],
  [{'headline': 'title'},{'headline': 'title'}]
}

Is it possible to use the news source column as the parent key? Not sure how to write this SQL query with the PG son syntax.

table

stories (
 news_source,
 headline
)

Answer 1

Don't aggregate the complete row, only the headline:

SELECT json_build_object(news_source, json_agg(headline))
FROM stories 
GROUP BY news_source
ORDER BY news_source;

Online example: http://rextester.com/LUOUR61576

Answer 2

Thank you!

I slightly modified your working code to return a set of records per each group by instead of just a single field.

  SELECT json_build_object(source, json_agg(stories.*))
  FROM stories 
  GROUP BY source
  ORDER BY source;