From: | Claudio Poli <masterkain(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | How to alias attributes in an ARRAY_AGG expression |
Date: | 2013-11-13 08:50:15 |
Message-ID: | CANp6QoKpiETR8G_f5_C5tKPE4gQNyMNXdNOr=3Sd6+t19P1zmA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello,
I'm playing with this query, my goal is return an entire json response with
some column names changed in the resultset of ARRAY_AGG::
SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
FROM (
SELECT
album,
MAX(release_year) AS release_year,
MAX(artwork_path) AS artwork_path,
MAX(MD5(CONCAT(album, release_year, artist))) AS token,
ARRAY_AGG((media_files.position, media_files.token) ORDER BY
media_files.position) as media_files
FROM media_files
INNER JOIN playlist_media_files ON playlist_media_files.media_file_id =
media_files.id
WHERE playlist_media_files.playlist_id = 1
GROUP BY album, release_year, artist
ORDER BY artist, release_year
) as ALBUM_ROW
This works fairly well, however I need to alias the attribute names in the
ARRAY_AGG:
ARRAY_AGG((media_files.position, media_files.token) ...
ARRAY_AGG apparently does not support AS, so I have to resort to a
subquery, which works but is totally inefficient:
SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
FROM (
SELECT
album,
MAX(release_year) AS release_year,
MAX(artwork_path) AS artwork_path,
MAX(MD5(CONCAT(album, release_year, artist))) AS token,
(
SELECT ARRAY_AGG(d)
FROM (
SELECT mf.position AS myalias
FROM media_files AS mf
INNER JOIN playlist_media_files ON
playlist_media_files.media_file_id = mf.id
WHERE playlist_media_files.playlist_id = #{playlist_id}
AND mf.album = media_files.album
ORDER BY mf."position" ASC
) d
) as media_files
FROM media_files
INNER JOIN playlist_media_files ON
playlist_media_files.media_file_id = media_files.id
WHERE playlist_media_files.playlist_id = #{playlist_id}
GROUP BY album, release_year, artist
ORDER BY artist, release_year
) as ALBUM_ROW
Does anyone have better ideas?
Thanks,
C
From | Date | Subject | |
---|---|---|---|
Next Message | Rama | 2013-11-13 14:08:52 | json and 9.3 function question |
Previous Message | Ishaya Bhatt | 2013-11-09 14:53:49 | Re: Datatype of a column |