[Django] #29157: Getting Distinct Values from List in a JSONField
Django
---------------------------------------------+------------------------
Reporter: Hrishikesh Barman | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
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 |
---------------------------------------------+------------------------
We can access JSONField Data
(https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#django.contrib.postgres.fields.JSONField)
like this `Dog.objects.filter(data__owner__name='Bob')`
But for JSONField consisting of a list this does not work.
{{{
[{'a':12,'b':33},{'a':44,'b':99}]
}}}
Filtering values like this works:
{{{
Frame.objects.filter(
size__contains=[{'a': 12,'b': 33}]
)
}}}
But there is no way to get distinct values from a list in a JSONfield, for
eg. the following does not work.
{{{
Frame.objects.values( 'size__a')
}}}
Should there be an implementation of the same?
--
Ticket URL: <https://code.djangoproject.com/ticket/29157>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-----------------------------------+--------------------------------------
Reporter: Hrishikesh Barman | Owner: (none)
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
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 Tim Graham):
I'm not sure if it's feasible. Do you know if the query can be expressed
in SQL?
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:1>
Django
-----------------------------------+------------------------------------
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
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 Tim Graham):
* stage: Unreviewed => Accepted
Comment:
Accepting for investigation if someone is interested, although I'm not
sure what change or if a change should be made here.
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:2>
Django
-----------------------------------+------------------------------------
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Hrishikesh Barman):
Replying to [comment:2 Tim Graham]:
> Accepting for investigation if someone is interested, although I'm not
sure what change or if a change should be made here.
I'll try working on this, but I won't tag myself on owned for now. But
will be checking what can be done.
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:3>
Django
-----------------------------------+------------------------------------
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Dmitry Dygalo):
The query could be expressed in this way:
{{{
# SELECT jsonb_array_elements('[{"a": 12, "b": 33}, {"a": 44,
"b":99}]'::jsonb) ->> 'a' AS "size";
size
------
12
44
(2 rows)
}}}
Also, `jsonb_array_elements` will fail if the value is not an array.
{{{
# SELECT jsonb_array_elements('{"x": [{"a": 12, "b": 33}, {"a": 44,
"b":99}]}'::jsonb) ->> 'a' AS "size";
ERROR: cannot extract elements from an object
}}}
Using it in the `WHERE` clause is a bit tricky:
{{{
# SELECT '[{"a": 12, "b": 33}, {"a": 12, "b":99}]'::JSONB AS "size" INTO
TEMPORARY test;
SELECT 1
# SELECT test.* FROM test, jsonb_array_elements(test.size) where value ->>
'b' = '99';
size
------------------------------------------
[{"a": 12, "b": 33}, {"a": 12, "b": 99}]
(1 row)
# SELECT test.* FROM test, jsonb_array_elements(test.size) where value ->>
'b' = '11';
size
------
(0 rows)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:4>
Django
-----------------------------------+------------------------------------
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Joey Wilhelm):
* cc: Joey Wilhelm (added)
Comment:
I have a similar use case. Using the following model...
{{{#!python
class Request(models.Model):
request_data = JSONField()
state = models.CharField(max_length=255)
}}}
And JSON data
{{{#!json
{"target": {"pk": 1}}
}}}
I'm able to issue a SQL query like this:
{{{#!sql
SELECT DISTINCT(request_data -> 'target' -> 'pk') FROM myapp_request WHERE
state = 'in_progress';
}}}
I would expect the equivalent ORM code to look like:
{{{#!python
Request.objects.filter(state=Request.STATE_IN_PROGRESS).values('request_data__target__pk').distinct()
}}}
OR
{{{#!python
Request.objects.filter(state=Request.STATE_IN_PROGRESS).annotate(target=F('request_data__target__pk')).values('target').distinct()
}}}
But both produce this error:
{{{#!python
django.core.exceptions.FieldError: Cannot resolve keyword 'target' into
field. Join on 'request_data' not permitted.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:5>
Django
-----------------------------------+------------------------------------
Type: New feature | Status: new
Component: contrib.postgres | Version: 2.0
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Fabian Köster):
* cc: Fabian Köster (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:6>
Django
-----------------------------------+--------------------------------------
Reporter: Hrishikesh Barman | Owner: (none)
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: needsinfo
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 felixxm):
* status: new => closed
* resolution: => needsinfo
* version: 2.0 => master
* stage: Accepted => Unreviewed
Comment:
Using key transforms in `.values()` and `.distinct()` was fixed in #24747.
I'm not sure if it's feasible to have querying for distinct values in
`JSONField` lists, e.g.
to get `<QuerySet [{'value__a': 12}, {'value__a': 'b'}]>` for
{{{
>>> JSONModel.objects.create(value=[{"a": 12, "b": 33}, {"a": 12,
"b":99}])
>>> JSONModel.objects.create(value={'a': 'b', 'c': 14})
}}}
Closing as needsinfo. I'm happy to reopen if we will get a PoC.
--
Ticket URL: <https://code.djangoproject.com/ticket/29157#comment:7>