Re: [Django] #29157: Allow querying for distinct values in JSONField lists. (was: Allow querying for distinct values in JSONField lists)

[Django]

#29157: Allow querying for distinct values in JSONField lists.
---+--
 Reporter:  Hrishikesh Barman  |Owner:  (none)
 Type:  New feature|   Status:  closed
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 `` 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.87f53d9705e01113cb44564de35907ec%40djangoproject.com.

Re: [Django] #29157: Allow querying for distinct values in JSONField lists

[Django]

#29157: Allow querying for distinct values in JSONField lists
---+
 Reporter:  Hrishikesh Barman  |Owner:  (none)
 Type:  New feature|   Status:  new
Component:  contrib.postgres   |  Version:  2.0
 Severity:  Normal |   Resolution:
 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 Fabian Köster):

 * cc: Fabian Köster (added)


-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.3eeef77c0d0c2a3a4fa4522ecb89%40djangoproject.com.

Re: [Django] #29157: Allow querying for distinct values in JSONField lists

[Django]

#29157: Allow querying for distinct values in JSONField lists
---+
 Reporter:  Hrishikesh Barman  |Owner:  (none)
 Type:  New feature|   Status:  new
Component:  contrib.postgres   |  Version:  2.0
 Severity:  Normal |   Resolution:
 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 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.edd8b06e25eb82e581ec603abc270d71%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29157: Allow querying for distinct values in JSONField lists

[Django]

#29157: Allow querying for distinct values in JSONField lists
---+
 Reporter:  Hrishikesh Barman  |Owner:  (none)
 Type:  New feature|   Status:  new
Component:  contrib.postgres   |  Version:  2.0
 Severity:  Normal |   Resolution:
 Keywords: | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.1020f6e5843f0702447f92446ad98ffb%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29157: Allow querying for distinct values in JSONField lists

[Django]

#29157: Allow querying for distinct values in JSONField lists
---+
 Reporter:  Hrishikesh Barman  |Owner:  (none)
 Type:  New feature|   Status:  new
Component:  contrib.postgres   |  Version:  2.0
 Severity:  Normal |   Resolution:
 Keywords: | Triage Stage:  Accepted
Has patch:  0  |  Needs documentation:  0
  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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.acbb2c5de3befee1bad6c46f513d41d3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29157: Allow querying for distinct values in JSONField lists

[Django]

#29157: Allow querying for distinct values in JSONField lists
---+
 Reporter:  Hrishikesh Barman  |Owner:  (none)
 Type:  New feature|   Status:  new
Component:  contrib.postgres   |  Version:  2.0
 Severity:  Normal |   Resolution:
 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: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.f9f12d288566d09ab25fd72f38dc6a86%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #29157: Allow querying for distinct values in JSONField lists (was: Getting Distinct Values from List in a JSONField)

[Django]

#29157: Allow querying for distinct values in JSONField lists
---+--
 Reporter:  Hrishikesh Barman  |Owner:  (none)
 Type:  New feature|   Status:  new
Component:  contrib.postgres   |  Version:  2.0
 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 Tim Graham):

 I'm not sure if it's feasible. Do you know if the query can be expressed
 in SQL?

-- 
Ticket URL: 
Django 
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/067.94465d9e6563fa1a0d3d432131e53270%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.