current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I feel like I need the 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 like

user | offer | clicks
-----|-------|--------
fred |coupons| 3
fred |cars   | 1
john |coupons| 2

But 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 1

Is there a way to do this in 9.3? Thank you!

share|improve this question
    
It might be relatively easy to extract the function and wrap it up in a C extension... –  Craig Ringer May 5 at 1:04
    
Can you use pl/v8 ? –  Clément Prévost May 30 at 18:50

1 Answer 1

up vote 2 down vote accepted

I was able to emulate json_object_agg using string_agg (which is available in 9.3).

Your example would be:

select user, ('{' || string_agg('"' || offer || '": ' || clicks, ',') || '}')::json as activity from click_activity group by user
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.