patternsqlMinor
Implementing json_object_agg() in Postgres 9.3
Viewed 0 times
implementingpostgresjson_object_agg
Problem
I feel like I need the
But I want to turn it into this: (aggregate the activity per user)
I think that the
Is there a way to do this in 9.3? Thank you!
json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table click_activity of data that looks likeuser | offer | clicks
-----|-------|--------
fred |coupons| 3
fred |cars | 1
john |coupons| 2But I want to turn it into this: (aggregate the activity per user)
user | activity
-----|----------
fred | {"coupons": 3, "cars": 1}
john | {"coupons": 2}I think that the
json_object_agg() function of Postgres 9.4 would do this perfectly, all I would have to call is select user, json_object_agg(offer, clicks) from click_activity group by 1Is there a way to do this in 9.3? Thank you!
Solution
I was able to emulate json_object_agg using string_agg (which is available in 9.3).
Your example would be:
Your example would be:
select user, ('{' || string_agg('"' || offer || '": ' || clicks, ',') || '}')::json as activity
from click_activity
group by userCode Snippets
select user, ('{' || string_agg('"' || offer || '": ' || clicks, ',') || '}')::json as activity
from click_activity
group by userContext
StackExchange Database Administrators Q#100607, answer score: 9
Revisions (0)
No revisions yet.