I'm trying to return pure JSON from a Postgres 9.2 table.
SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW)) FROM ( SELECT album, max(release_year) AS release_year, max(artwork_path) AS artwork_path, MD5(concat(album,release_year,artist)) AS token, ARRAY_AGG((media_files.position, media_files.token, media_files.title) 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 query works fairly well, however on the line:
ARRAY_AGG((media_files.position, media_files.token) ORDER ...) as media_files
I would like to alias
token attributes in the result set.
AS apparently it's not permitted here.
I would have written:
ARRAY_AGG((media_files.position AS xxx, media_files.token AS yyy) ORDER BY media_files.position) as media_files
But it's not working.
Postgres documentations says: "where aggregate_name is a previously defined aggregate (possibly qualified with a schema name), expression is any value expression that does not itself contain an aggregate expression or a window function call, and order_by_clause is a optional ORDER BY clause as described below."
Am I out of luck?