Return Postgres json_build_object as variable field from table

Question

postgesql returns the json_build_object as a parent for each grouped json array like this:

 {
  "stories": [{
        "json_build_object": {
          "CNN": []
        }
      },
      {
        "json_build_object": {
          "FOX": []
        }
      },
      {
        "json_build_object": {
          "Huffpost": []
        }
      },...

Postgresql returns the "json_build_object" as a key. Is it possible to replace with the stories.source value returned by the group by? Tried an ALIAS but that returned an error.

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

Optimal solution would be a response like this:

 stories:
  CNN: [],
  FOX: []...

I'm sure I'm missing a best practice for returning JSON in Postgresql...

SELECT json_object_agg(source, strs) FROM (SELECT source, array_agg(stories.*) as strs FROM stories GROUP BY stories.source ORDER BY source) as t;? — Abelisto
– Abelisto, Commented Jul 25, 2017 at 18:57
Thanks @Abelisto ! would upvote if you post it as an answer. — Kevin Amiranoff
– Kevin Amiranoff, Commented Jan 13, 2020 at 23:42

Durgpal Singh · Accepted Answer · 2018-10-04 07:30:30Z

1

Use json_agg before json_object_agg like this.

select test json_agg(json_oject_agg(...)) test;

answered Oct 4, 2018 at 7:30

Durgpal Singh

12k4 gold badges40 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

Return Postgres json_build_object as variable field from table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related