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?

Answer 1

Turns out the problem was two-fold.

1. Cleaning out the Null values could be done through using array_remove
2. 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