row_to_json on a subset of columns.

From: Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: row_to_json on a subset of columns.
Date: 2014-05-30 16:16:02
Message-ID: CAK7KUdB64hTsWqBWy9R-g+mebajSra3Uz73zQuMxCyQLYLHUQA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Thanks!
Chris

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2014-05-30 16:19:55 Re: Fwd: libpq: indefinite block on poll during network problems
Previous Message Dmitry Samonenko 2014-05-30 15:48:00 Re: Fwd: libpq: indefinite block on poll during network problems