Implementing json_object_agg() in Postgres 9.3

Question

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!

• Craig Ringer: It might be relatively easy to extract the function and wrap it up in a C extension...
• Clément Prévost: Can you use pl/v8 ?

Answer 1

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

• jpmc26: 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: 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.

Answer 2

Use insteade of json_object_agg => json_agg

select user, json_agg((offer, clicks)) from click_activity group by 1

• Md Haidar Ali Khan: ,Welcome to the StackExchange. Kindly add some notes related to query , for better understanding.