sub-select with multiple records, columns

From: Israel Brewster <israel(at)ravnalaska(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: sub-select with multiple records, columns
Date: 2017-06-19 20:17:32
Message-ID: 34A99D9B-819D-404A-BABB-AF0CC29AF351@ravnalaska.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have two tables, a ticket table and a notes table, set up where each ticket can have multiple notes. I'm trying to come up with a query that returns the ticket fields as well as a field that is an array type field with the values being json-encoded note records. I've come up with the following subselect query, which works:

SELECT
...
(SELECT
array_agg(to_json(row(notedate,username,note)))
FROM sabrenotes
INNER JOIN users ON author=users.id
WHERE ticket=sabretickets.id ) notes
FROM tickets
WHERE ...

The only problem with this query is that the notes aren't sorted. Of course, simply adding an ORDER BY clause to the sub-select doesn't work - it throws an error about needing to use notedate in a GROUP BY clause or aggregate function. Is there some way I can get sorting as well here? Of course, I could just run a second query to get the notes, and combine in code, but that's no fun... :-)
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2017-06-19 20:29:12 Re: sub-select with multiple records, columns
Previous Message Dmitry O Litvintsev 2017-06-19 19:53:55 Re: autovacuum holds exclusive lock on table preventing it from to be updated