That works! Thanks! On Fri, May 30, 2014 at 11:59 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > On Fri, May 30, 2014 at 11:16 AM, Chris Hanks > <christopher.m.hanks@xxxxxxxxx> wrote: >> I'm using a JSON column to store some aggregate data, like so: >> >> UPDATE courses_table >> SET aggregates = agg.aggregates >> FROM ( >> SELECT course_id, row_to_json(sub) AS aggregates >> FROM ( >> SELECT course_id, avg(rating) AS rating, sum(reviews_count) AS >> reviews_count, >> sum(user_started_count) AS user_started_count, >> sum(all_user_started_count) AS all_user_started_count, >> sum(user_completed_count) AS user_completed_count, >> sum(all_user_completed_count) AS all_user_completed_count >> FROM course_details_table >> GROUP BY course_id >> ) sub >> ) agg >> WHERE courses_table.id = agg.course_id; >> >> This works, but also stores the course_id in the JSON document. Is >> there a relatively clean way to remove it? The suggestions I got in >> #postgresql on freenode were to remove the course_id from the >> innermost select, but that would break the outer queries, or to use >> row() to select only a few of the columns, which loses their column >> names. I'm on PG 9.3.3. > > easy. whenever you are tempted to use row(), just push to subquery > and row to json the inner table expression: > > UPDATE courses_table > SET aggregates = agg.aggregates > SELECT course_id, row_to_json((select q from (select rating, > reviews_count) q)) AS aggregates > FROM ( > SELECT course_id, avg(rating) AS rating, sum(reviews_count) AS > reviews_count, > sum(user_started_count) AS user_started_count, > sum(all_user_started_count) AS all_user_started_count, > sum(user_completed_count) AS user_completed_count, > sum(all_user_completed_count) AS all_user_completed_count > FROM course_details_table > GROUP BY course_id > ) sub ) agg > WHERE courses_table.id = agg.course_id; > > merlin
