From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: row_to_json on a subset of columns. |
Date: | 2014-05-30 18:59:09 |
Message-ID: | CAHyXU0yqZ_wpdGtvyHgEBY9wtgxb_K8_6J9t9+n_37D0kBhTAg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, May 30, 2014 at 11:16 AM, Chris Hanks
<christopher(dot)m(dot)hanks(at)gmail(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-05-30 18:59:11 | Re: [GENERAL] unable to build postgres-9.4 in os x 10.9 with python |
Previous Message | Tom Lane | 2014-05-30 18:52:48 | Re: [GENERAL] unable to build postgres-9.4 in os x 10.9 with python |