From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Claudio Poli <masterkain(at)gmail(dot)com> |
Cc: | pgsql novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: How to alias attributes in an ARRAY_AGG expression |
Date: | 2013-11-13 15:09:07 |
Message-ID: | CAHyXU0xhWHw-YYXbc3OFH+u0G=gD4Kq66aKKoZnDNDBXj7Bzkw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Wed, Nov 13, 2013 at 2:50 AM, Claudio Poli <masterkain(at)gmail(dot)com> wrote:
> 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:
Well, for starters, define "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?
Try creating a composite type and caseting:
> ARRAY_AGG((media_files.position, media_files.token) ORDER BY
> media_files.position) as media_files
could become
ARRAY_AGG((media_files.position, media_files.token) ORDER BY
media_files.position)::foo[] as media_files
where foo is the type with the names as you want them. Also, when not
grouping, don't be afraid to try the array() constructor syntax:
select
a,
array(
select b from b where b.id = a.id order by ...
)
...
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Claudio Poli | 2013-11-13 17:25:54 | Re: How to alias attributes in an ARRAY_AGG expression |
Previous Message | Rama | 2013-11-13 14:08:52 | json and 9.3 function question |