Re: How to alias attributes in an ARRAY_AGG expression - Mailing list pgsql-novice

On Wed, Nov 13, 2013 at 2:50 AM, Claudio Poli <masterkain@gmail.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