[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] row_to_json on a subset of columns.
From: Chris Hanks <christopher.m.hanks () gmail ! com>
Date: 2014-05-30 19:05:14
Message-ID: CAK7KUdCVh8zawdTw2e8EU8Tdrjeqk0qqnupBqHaUo9jvgU71pQ () mail ! gmail ! com
[Download RAW message or body]
That works! Thanks!
On Fri, May 30, 2014 at 11:59 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, May 30, 2014 at 11:16 AM, Chris Hanks
> <christopher.m.hanks@gmail.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic