Re: How to alias attributes in an ARRAY_AGG expression
От | Merlin Moncure |
---|---|
Тема | Re: How to alias attributes in an ARRAY_AGG expression |
Дата | |
Msg-id | CAHyXU0xhWHw-YYXbc3OFH+u0G=gD4Kq66aKKoZnDNDBXj7Bzkw@mail.gmail.com обсуждение исходный текст |
Ответ на | How to alias attributes in an ARRAY_AGG expression (Claudio Poli <masterkain@gmail.com>) |
Ответы |
Re: How to alias attributes in an ARRAY_AGG expression
|
Список | 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
В списке pgsql-novice по дате отправления: