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!

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

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

Use insteade of json_object_agg => json_agg

select user, json_agg((offer, clicks)) from click_activity group by 1
  • 1
    ,Welcome to the StackExchange. Kindly add some notes related to query , for better understanding. – Md Haidar Ali Khan Oct 7 '18 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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