Postgres convert json with duplicate IDs

Question

With this select:

json_agg(json_build_object("id", price::money))

I get the resulting value:

[
  {"6" : "$475.00"}, 
  {"6" : "$1,900.00"},
  {"3" : "$3,110.00"},
  {"3" : "$3,110.00"}
]

I would like the data in this format instead:

{
  "6": ["$475.00","$1,900.00"],
  "3": ["$3,110.00","$3,110.00"]
}

When queried on the server or used with jsonb, the IDs are duplicate and only one of the key value pairs make it through.

Maybe array_agg(price::money) fixes it but a bit hard to tell without example data.. — Raymond Nijland
– Raymond Nijland, Commented May 31, 2019 at 22:09

klin · Accepted Answer · 2019-06-01 00:00:29Z

6

You should aggregate prices in groups by ids and use the aggregate function json_object_agg(). You have to use a derived table (subquery in the from clause) because aggregates cannot be nested:

select json_object_agg(id, prices)
from (
    select id, json_agg(price::money) as prices
    from my_table
    group by id
    ) s

Working example in rextester.

edited Jun 1, 2019 at 0:00

answered May 31, 2019 at 22:50

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

that_guy Over a year ago

Everything is wrapped in an array:[{"6" : ["$475.00", "$1,900.00"]}, {"3" : ["$3,110.00", "$3,110.00"]}], i'd like it to just be an object: {"6" : ["$475.00", "$1,900.00"], "3" : ["$3,110.00", "$3,110.00"]}

that_guy Over a year ago

I realized I had formatted my question wrong, I've edited it to reflect that comment. Sorry!

Collectives™ on Stack Overflow

Postgres convert json with duplicate IDs

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