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
  • This will not perform escapes properly. If the values contain quote marks or other characters with special meanings in JSON, this will either throw an error or generate an incorrect result. – jpmc26 Oct 8 at 18:14
  • This can be fixed be replacing the manual quoting with just a to_json call: ('{' || string_agg(to_json(offer) || ': ' || to_json(clicks), ',') || '}')::json. Double quotes are added automatically around the offer value when concatenating it. – jpmc26 Oct 8 at 18:31

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.