HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Implementing json_object_agg() in Postgres 9.3

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
implementingpostgresjson_object_agg

Problem

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!

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#100607, answer score: 9

Revisions (0)

No revisions yet.