Django queryset StringAgg on arrayfield

Question

I have some data which includes sizes, much like the model below.

class Product(models.Model):
    width = models.CharField()
    height = models.CharField()
    length = models.CharField()

Through annotation we have a field called at_size which produces data like:

[None, None, None]
['200', '000', '210']
['180', None, None]

This was accomplished like so (thanks to: )https://stackoverflow.com/a/70266320/5731101:

    class Array(Func):
        template = '%(function)s[%(expressions)s]'
        function = 'ARRAY'

    out_format = ArrayField(CharField(max_length=200))

    annotated_qs = Product.objects.all().annotate(
        at_size=Array(F('width'), F('height'), F('length'), 
            output_field=out_format)
    )

I'm trying to get this to convert into:

''
'200 x 000 x 210'
'180'

In code, this could a bit like ' x '.join([i for i in data if i]). But as I need to accomplish this with database functions it's a bit more challenging.

I've been playing with StringAgg, but I keep getting:

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

It looks like I need to make sure the None values are excluded from the initial Array-func to begin with. But I'm not sure where to get started here. How can I accomplish this?

What database do you use? – Willem Van Onsem Dec 08 '22 at 13:41 — Willem Van Onsem, Dec 08 '22 at 13:41
What about the `Concat` db function? – Swift Dec 08 '22 at 15:08 — Swift, Dec 08 '22 at 15:08

S.D. · Accepted Answer · 2022-12-09T15:54:32.167

Turns out the problem was two-fold.

Cleaning out the Null values could be done through using array_remove
glueing the strings with a delimiter through StringAgg only works if the input are strings. But since we use an array, that wasn't the way to go. Instead use array_to_string

The final result looks like:

    class Array(Func):
        # https://www.postgresql.org/docs/9.6/functions-array.html
        template = '%(function)s[%(expressions)s]'
        function = 'ARRAY'

    class ArrayRemove(Func):
        # https://www.postgresql.org/docs/9.6/functions-array.html
        function = 'array_remove'

    class ArrayToString(Func):
        # https://stackoverflow.com/a/57873772/5731101
        function = 'array_to_string'

    out_format = ArrayField(CharField(max_length=200))

    annotated_qs = annotated_qs.annotate(
        at_size=ArrayToString(
            ArrayRemove(
                Array(F('width'), F('height'), F('length'), output_field=out_format),
                None, # Remove None values from the Array with ArrayRemove                
            ),
            Value(" x "),  # Delimiter.
            Value(''),  # If there are null-values, replace with... (fallback)
            output_field=CharField(max_length=200),
        )
    )

this produces the desired format:

for product in annotated_qs:
    print(product.at_size)

180 x 000 x 200
180 x 026 x 200
180 x 7 x 200
180 x 000 x 200
200 x 000 x 220
180 x 000 x 200
175 x 230 x 033
160 x 000 x 200

60 x 220

ty :). Was quite the search, but turned out to stare me right in the face...Note to self: read the docs... — S.D., Dec 09 '22 at 15:56

score 0 · Answer 2 · answered Dec 08 '22 at 15:18

0

Product.objects.annotate(display_format=Concat(F('width'), '×', F('height'), '×', F('length')))

Should do the trick no?

No need to over complicate this, let's keep it nice and simple and use the database to concatenate the 3 strings and separate them with the multiplication symbol (obviously replace if you prefer another character).

Take a look at the docs over here: https://docs.djangoproject.com/en/4.1/ref/models/database-functions/#concat

answered Dec 08 '22 at 15:18

Swift

1,663
1
10
21

Unfortunately that would produce " x x x" when there are all null values. – S.D. Dec 08 '22 at 16:17
Ok. Let me revise my answer slightly as I'm sure there's a way :) – Swift Dec 09 '22 at 08:55
So far, I've managed to exclude the null-values by using array_remove. But still getting the same error on StringAgg even though the array only contain strings. – S.D. Dec 09 '22 at 10:24

Django queryset StringAgg on arrayfield

2 Answers2