row_to_json on a subset of columns.

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

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