postgresql build_json_object returns

Question

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

{
  "status": "success",
  "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?

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

Ok, then how did that json_build_object attribute get added? I noticed this occurs when using db.func() syntax as well. — nick
– nick, Commented Jul 20, 2017 at 15:26
That's what you get from the query. And it is completely outside of pg-promise. — vitaly-t
– vitaly-t, Commented Jul 20, 2017 at 15:44
Thanks for the secondary clarification! Have updated original post to postgresql specific. Thanks! — nick
– nick, Commented Jul 21, 2017 at 1:52

vitaly-t · Accepted Answer · 2017-07-21 07:12:09Z

2

There must be a way to do this in SQL, but for the lack of it now, you can convert that stories property into the right object:

function convert(stories) {
    const res = {};
    for (let i = 0; i < stories.length; i++) {
        const obj = stories[i].json_build_object;
        const name = Object.keys(obj)[0];
        res[name] = obj[name];
    }
    return res;
}

answered Jul 21, 2017 at 7:12

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

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

2 Comments

nick Over a year ago

Thanks Vitaly, this is what I had planned to do with no SQL option found yet. Will post the solution when I figure it out.

Pete TNT Over a year ago

Anyone figured out that out yet? Ran into the same issue myself.

Collectives™ on Stack Overflow

postgresql build_json_object returns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related