[Django] #32906: Explain JSONBAgg benefit
Django
------------------------------------------------+------------------------
Reporter: Claude Paroz | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: dev
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------------+------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32906>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Claude Paroz | Owner: nobody
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* cc: Mads Jensen (added)
Comment:
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 `JSONField`s with `JSONBAgg()` then you can use key and
index transforms which don't work with `ArrayAgg()` annotations.
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:1>
Django
-------------------------------------+-------------------------------------
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Claude Paroz):
Then adding tests/docs with calling `JSONBAgg` on a `JSONField` would be
great.
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:2>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: dev
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* stage: Unreviewed => Accepted
Comment:
Agreed.
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:3>
Django
--------------------------------------+------------------------------------
Type: Cleanup/optimization | Status: assigned
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Abhyudai):
* owner: nobody => Abhyudai
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:4>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Abhyudai):
Replying to [comment:1 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 `JSONField`s 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.
{{{#!python
# model
class AggregateTestModel(PostgreSQLModel):
"""
To test postgres-specific general aggregation functions
"""
json_field = models.JSONField(null=True)
}}}
{{{#!sh
# 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:5>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Mariusz Felisiak):
Replying to [comment:5 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
[https://code.djangoproject.com/ticket/32906#comment:1 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...
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:6>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Abhyudai:
Old description:
> 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.
New description:
Thanks for the explanation, Mariusz.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:7>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Description changed by Abhyudai:
Old description:
> Thanks for the explanation, Mariusz.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:8>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Abhyudai):
I had mistakenly put my reply in to modify ticket box. Sorry for the
noise.
Thanks, Mariusz, for the explanation.
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:9>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Abhyudai):
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/14640 pull-request]
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:10>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:11>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Abhyudai):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:12>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:13>
Django
--------------------------------------+------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Abhyudai):
* needs_better_patch: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:14>
Django
-------------------------------------+-------------------------------------
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Claude Paroz | Owner: Abhyudai
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"5a634a7b6f248e5dead65f420ec5d30acd9724bf" 5a634a7b]:
{{{
#!CommitTicketReference repository=""
revision="5a634a7b6f248e5dead65f420ec5d30acd9724bf"
Fixed #32906 -- Added docs and tests for using key and index lookups on
JSONBAgg results.
Co-authored-by: Mariusz Felisiak <felisiak...@gmail.com>
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32906#comment:16>