sorting within a nested array_agg query

Question

My goal with this query is to pull everything from the communities table, as well as an array_agg of jsonb objects from a join. This works okay in the unsorted case thusly:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id))
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
        where conversations.published = true
        and community_permissions.community_id = communities.id
  ) as conversations
  from communities where communities.id = 110;

this will return a row with normal columns for the columns in communities, and a json array that looks like [{ id: 1 }, { id: 2 } ...] in the conversations column. All is well and good, the query works pretty well and I can declaratively in SQL fetch a row and its important relations all at once. I can even filter the returned value on a join from a different table (only published conversations are returned in this list).

What I want to do now is sort the conversations based on an attribute in the documents table (aliased to conversations above). The naive approach:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id))
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
        where conversations.published = true
        and community_permissions.community_id = communities.id
        order by conversations.updated_at desc
  ) as conversations
  from communities where communities.id = 110;

does not work - the column walker wants the conversations.updated_at value to be in the group_by clause. If I add a group by clause like so:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id))
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
        where conversations.published = true
        and community_permissions.community_id = communities.id
        group by conversations.updated_at
        order by conversations.updated_at desc
  ) as conversations
  from communities where communities.id = 110;

It looks like the array_agg no longer actually aggregates the response - I get an error "more than one row returned by a subquery used as an expression". I can put a limit 1 after the order by, and the query executes, but then I only get one conversation in the array_agg column - the most recent one at least. So it seems like array_agg isn't actually agging into an array, so to speak.

I'm able to do this query by taking the whole thing apart and using a CTE:

with q1 as (
  select conversations.id as id, community_permissions.community_id as community_id
  from documents as conversations
  join community_permissions on community_permissions.document_id = conversations.id
  where conversations.published = true order by conversations.updated_at desc
)
select communities.id, (
  select array_agg(jsonb_build_object('id', q1.id))) as conversations 
  from communities join q1 on q1.community_id = communities.id
  where communities.id = 110
  group by communities.id;

but that's pretty inefficient, the explainer indicates that it's sorting the entire conversations table each time the query runs, and any given community only has a small percentage of the conversations table shared with it via community_permissions.

It is also unsatisfying because I am trying to modularize my queries so the backend can decide, "hey, you're going to need these values" and shove them into the SQL query and do everything all at once in a single query, instead of pulling the community and then manually querying for each relationship (there's usually more than just one - communities have members, conversations, followers, etc). Worst case, I can do this - just handle the extra fetching in the backend node layer and assemble there, but putting it all in subqueries seemed more elegant (until I needed this sort feature).

pfooti · Accepted Answer · 2017-10-17 18:25:20Z

3

one of those "write up a long post on stackoverflow and find the answer ten minutes later" situations.

The trick, for future reference, is that I was putting the order by in the wrong place. This works:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id) order by conversations.updated_at desc)
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
      where conversations.published = true
      and community_permissions.community_id = communities.id
  ) as conversations
  from communities where communities.id = 110;

note that the order by is now in the array_agg call rather than inside the subquery.

answered Oct 17, 2017 at 18:25

pfooti

2,7144 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marth · Accepted Answer · 2017-10-17 18:25:29Z

2

You can specify the order in an aggregate function.

Try:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id) order by conversations.updated_at desc)
    from documents as conversations
    join community_permissions on community_permissions.document_id = conversations.id
    where conversations.published = true
      and community_permissions.community_id = communities.id
  ) as conversations
from communities where communities.id = 110;

answered Oct 17, 2017 at 18:25

Marth

24.9k3 gold badges68 silver badges76 bronze badges

Collectives™ on Stack Overflow

sorting within a nested array_agg query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related