1

Hoping I could annotate a QuerySet with an array aggregate, I tried the following:

Item.objets.annotate(
    results=ArrayAgg(
        RawSQL("row(field_a, field_b)", []),
            output_field=ArrayField(ArrayField(models.IntegerField())))
    )
)

And it basically works except that – unless I'm returning a 1-dimensional array – I only get strings of PostgreSQL's array representations as a return value:

'{"(30,8686)"}' instead of [(30,8686)]

Does anyone have an Idea how to get proper Python lists that I can iterate through in the template?

jnns
  • 5,148
  • 4
  • 47
  • 74
  • I'm grouping results together that each have a value (`field_a`) and an event id (`field_b`). I don't want to iterate over each row in the template to avoid N+1 performance issues. ArrayAgg() does exactly that, but as I don't just want a list but a list of tuples I also need `row()` to put two values in the array. – jnns Aug 15 '16 at 09:18
  • Please rest assured that this is not premature optimization. The example above is very reduced. In reality there are about 300 Items returned where I do not want to run a separate query for each of them just to get the list of sub-items. Without this aggregation, the view is slowed down by seconds. – jnns Aug 15 '16 at 09:44
  • Databases are optimized for exactly this use case. All the heavy-lifting should occur in the database. See: [Do databsae work in the database rather than in Python](https://docs.djangoproject.com/en/1.10/topics/db/optimization/#do-database-work-in-the-database-rather-than-in-python) – jnns Aug 15 '16 at 10:13
  • What they are talking about is very different from this use case is my opinion. Did you actually try running the sql generated from your current query in the console and do an an explain analyze on it. Then try your array agg query (in it's raw form) and see if there is indeed a big difference in results – e4c5 Aug 15 '16 at 10:57
  • Have you tried using `model.objects.values_list()`? – Piyush S. Wanare Nov 14 '16 at 09:52
  • I can't even find ArrayAgg well documented anywhere let alone ArrayField and would love to find some decent documentation. AKA I share you problem, only worse, I can't get any return (yet). – Bernd Wechner Mar 16 '21 at 12:08

0 Answers0