Search Postgresql Archives

Re: row_to_json on a subset of columns.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux