How Can I Index an Array Produced by array_agg in Peewee

Question

I'm trying to access the first element of an array that is contained within a peewee .select_from and created by a postgresql array_agg.

Here is a simplified version of the query:

rows = list(ST.select_from(fn.array_agg(ST.c.sitename)[1].alias("sitename"))
 .join(LS, on=ST.c.id == LS.site)
 .join(L, on=LS.location == L.id)
 .group_by(L).with_cte(ST).objects().dicts())

Sites (ST) and locations (L) have a many-to-many relationship through an intermediary table LS. ST is a cte because it is a filtered down version of the Site table with certain criteria.

The relevant SQL being returned here is

 SELECT (array_agg("ST"."sitename") = 1) AS "sitename"

Instead I want the sql to be

SELECT (array_agg("ST"."sitename"))[1] AS "sitename"

It seems that you can index into an ArrayField using [] from the Googling I did, but I'm assuming the result of fn.array_agg() isn't an ArrayField. I would like to know how to index into the results of an fn.array_agg(), or how to convert it into an ArrayField in order to index into it using [].

Answer 1

This is annoyingly obtuse with Peewee at present - my apologies. Part of this is due to Postgres' insistence that the function be wrapped in parentheses before it can be indexed -- peewee tries to eliminate redundant parentheses, which forces an additional workaround. At any rate, here is one way:

p1, p2, p3 = [Post.create(content='p%s' % i) for i in '123']
Comment.create(post=p1, comment='p1-c1')
Comment.create(post=p1, comment='p1-c2')
Comment.create(post=p2, comment='p2-c1')

idx = NodeList([
    SQL('(array_agg('),
    Comment.comment,
    SQL('))[%s]', (1,))])

query = (Post
         .select(Post, idx.alias('comment'))
         .join(Comment, JOIN.LEFT_OUTER)
         .group_by(Post)
         .order_by(Post.content))
# p1 p1-c1
# p2 p2-c1
# p3 None