Explain JSONBAgg benefit

[Claude Paroz]

When reading current Django docs and tests, I can't see how JSONBAgg has any advantage over ArrayAgg. It would be great to add both in docs and tests examples showing benefits/differences between ArrayAgg and JSONBAgg, if any.

[Mariusz Felisiak]

I don't think there is a significant difference between them. ARRAY_AGG() returns array and JSONB_AGG() return JSON array, so when you aggregate JSONFields with JSONBAgg() then you can use key and index transforms which don't work with ArrayAgg() annotations.

[Claude Paroz]

Then adding tests/docs with calling JSONBAgg on a JSONField would be great.

[Mariusz Felisiak]

Agreed.

[Abhyudai]

Replying to Mariusz Felisiak:

I don't think there is a significant difference between them. ARRAY_AGG() returns array and JSONB_AGG() return JSON array, so when you aggregate JSONFields with JSONBAgg() then you can use key and index transforms which don't work with ArrayAgg() annotations.

As far I can see, they both are giving the same results.

# model

class AggregateTestModel(PostgreSQLModel):
    """
    To test postgres-specific general aggregation functions
    """
    json_field = models.JSONField(null=True)

# shell

>>> AggreTestModel.objects.bulk_create(
     AggregateTestModel(json_field={'lang': 'pl'}),
     AggregateTestModel(json_field={'lang': 'en'}),
     AggregateTestModel(json_field={'breed': 'collie'})
)

>>> AggregateTestModel.objects.all().values_list('json_field', flat=True)
<QuerySet [None, {'lang': 'pl'}, {'lang': 'en'}, {'breed': 'collie'}]>
>>> AggregateTestModel.objects.aggregate(val=JSONBAgg('json_field'))
{'val': [None, {'lang': 'pl'}, {'lang': 'en'}, {'breed': 'collie'}]}
>>> jsonb =  AggregateTestModel.objects.aggregate(val=JSONBAgg('json_field'))
>>> jsonb
{'val': [None, {'lang': 'pl'}, {'lang': 'en'}, {'breed': 'collie'}]}
>>> arr = AggregateTestModel.objects.aggregate(val=ArrayAgg('json_field'))
>>> arr
{'val': [None, {'lang': 'pl'}, {'lang': 'en'}, {'breed': 'collie'}]}
>>> arr == jsonb
True
>>> type(arr)
<class 'dict'>
>>> type(jsonb)
<class 'dict'>

Am I missing something here? The above snippet is just an abstracted version of the model inside the package postgres_tests.models, used for tests.

[Mariusz Felisiak]

Replying to Abhyudai:

Am I missing something here? The above snippet is just an abstracted version of the model inside the package postgres_tests.models, used for tests.

They are not the same on the PostgreSQL-level because ARRAY_AGG() returns array and JSONB_AGG() returns JSON array, see my comment. For example:

>>> AggregateTestModel.objects.annotate(jsonbagg=JSONBAgg('json_field')).filter(jsonbagg__0__lang='en').values('jsonbagg')
<QuerySet [{'jsonbagg': [{'lang': 'en'}]}]>
>>> AggregateTestModel.objects.annotate(arrayagg=ArrayAgg('json_field')).filter(arrayagg__0__lang='en').values('arrayagg')
  File "/django/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.SyntaxError: syntax error at or near "["
LINE 1: ..."postgres_tests_aggregatetestmodel"."json_field" )[1] -> 'la...

[Abhyudai]

I had mistakenly put my reply in to modify ticket box. Sorry for the noise.

Thanks, Mariusz, for the explanation.

[Abhyudai]

​pull-request

[Mariusz Felisiak <felisiak.mariusz@…>]

In 5a634a7b:

Fixed #32906 -- Added docs and tests for using key and index lookups on JSONBAgg results.

Co-authored-by: Mariusz Felisiak <felisiak.mariusz@…>