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
)

user330315 · Accepted Answer · 2017-07-18 05:29:35Z

14

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

answered Jul 18, 2017 at 5:29

user330315

Sign up to request clarification or add additional context in comments.

Comments

nick · Accepted Answer · 2017-07-20 00:45:17Z

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;

answered Jul 20, 2017 at 0:45

nick

3763 silver badges17 bronze badges

Collectives™ on Stack Overflow

Postgres return json with group

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related