How to define alias in an ARRAY_AGG expression?

Question

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?

Answer 1

You are forming an ad-hoc row type (effectively an anonymous record) with this expression:

(media_files.position, media_files.token, media_files.title)

in your aggregate function call:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)
          ORDER BY media_files.position) AS media_files

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:

CREATE TYPE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

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:

CREATE TEMP TABLE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

Either way, you can then cast your record:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files

Then you can reference elements of the (now well-known) type by name:

SELECT media_files[1].position, media_files[1].token
FROM  (
     ...
   ,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files
   ...
   FROM ....
   GROUP BY ...
  ) sub;

Now, Postgres can use these names for building a JSON value. Voilá.

Answer 2

I don't have Postgres on my machine, and I really don't know it all that well, but it seems to me you could alias the desired columns through a subquery something like this:

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((mf.PositionAlias, mf.TokenAlias, mf.TitleAlias) ORDER BY mf.PositionAlias) as media_files
    FROM (
        SELECT
        album,
        max(release_year) AS release_year,
        max(artwork_path) AS artwork_path,
        MD5(concat(album,release_year,artist)) AS token,
        position as PositionAlias,
        token as TokenAlias,
        title as TitleAlias
        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 mf
  ) as ALBUM_ROW;