Thread: How to alias attributes in an ARRAY_AGG expression
Hello,
SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
FROM (
SELECT
album,
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
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::
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_filesFROM 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
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
On Wed, Nov 13, 2013 at 4:09 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
[snip]
> Well, for starters, define "totally inefficient".
I meant that it takes long time compared to my first alternative, which I not posted, due to the subquery, there should be no need for that if I can alias the current resultset with just array_agg(fields)
> 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:
Thanks for the suggestion, I tried but I wasn't able to set an alias, I tried something horrific like this:
create type media_file_detail as (position integer AS myalias);
and Google failed me to provide some answers.
select
a,
array(
select b from b where b.id = a.id order by ...
)
Thanks, will do
c
Claudio Poli wrote > create type media_file_detail as (position integer AS myalias); CREATE TYPE media_file_detail AS (position_alias integer, token_alias text); ... ARRAY_AGG( (media_files.position, media_files.token)::media_file_detail ) ... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-alias-attributes-in-an-ARRAY-AGG-expression-tp5778089p5778196.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Awesome, thank you very much.
C.
On Wed, Nov 13, 2013 at 6:39 PM, David Johnston <polobo@yahoo.com> wrote:
Claudio Poli wrote
> create type media_file_detail as (position integer AS myalias);
CREATE TYPE media_file_detail AS (position_alias integer, token_alias text);
... ARRAY_AGG(
(media_files.position, media_files.token)::media_file_detail
) ...
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-alias-attributes-in-an-ARRAY-AGG-expression-tp5778089p5778196.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice