Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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á.

share|improve this answer

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;
share|improve this answer
    
hello, I tried something similar but position is ambiguous, after I fixed it column "media_files.position" must appear in the GROUP BY clause or be used in an aggregate function. I'm currently experimenting with this but it seems over the top: pastie.org/private/kqksctmbgognytkeqovbya –  John Smith Nov 13 '13 at 7:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.