CODEWITHSUNDEEP
jsonpostgresql
nick
nick

Reputation: 376

Return Postgres json_build_object as variable field from table

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...

Upvotes: 1

Views: 1837

Answers (1)

Durgpal Singh
Durgpal Singh

Reputation: 11983

Use json_agg before json_object_agg like this.

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

Upvotes: 1

Related Questions