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 position
and 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."
http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-AGGREGATES
Am I out of luck?
Best Answer
You are forming an ad-hoc row type (effectively an anonymous record) with this expression:
in your aggregate function call:
Arrays types can only be built upon well-known types. Your option is to announce such a type to the system and cast the record to it before forming the array. Create a well-known composite type:
I am guessing data types for lack of information here. Fill in your actual types.
Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:
Either way, you can then cast your record:
Then you can reference elements of the (now well-known) type by name:
Now, Postgres can use these names for building a JSON value. Voilá.