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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With