Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+
 Reporter:  eoinmurray|Owner:  nobody
 Type:  Cleanup/optimization  |   Status:  new
Component:  Documentation |  Version:  master
 Severity:  Normal|   Resolution:
 Keywords:  postgres, jsonb   | Triage Stage:  Accepted
Has patch:  1 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+
Changes (by jensneuhaus):

 * has_patch:  0 => 1


Comment:

 Hi. I submitted a patch to include it in the docs:
 https://github.com/django/django/pull/6965

--
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/068.8985507edc2011f8fe3187cd973c9bf1%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+
 Reporter:  eoinmurray|Owner:  nobody
 Type:  Cleanup/optimization  |   Status:  new
Component:  Documentation |  Version:  master
 Severity:  Normal|   Resolution:
 Keywords:  postgres, jsonb   | Triage Stage:  Accepted
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+

Comment (by ckarrie):

 I've done something like:

 {{{
 like_arg = "%%%s%%" % query_string
 qs.extra(where=["json_values ->> %s LIKE %s"], params=[lookup_key,
 like_arg])
 }}}

 json_values is my JSONField, lookup_key is "title" in your case, like_arg
 is "%moon%" in your case.

 Thus returns a queryset

--
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/068.381a1b8e45f26fd4d065f552136bddcd%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+
 Reporter:  eoinmurray|Owner:  nobody
 Type:  Cleanup/optimization  |   Status:  new
Component:  Documentation |  Version:  master
 Severity:  Normal|   Resolution:
 Keywords:  postgres, jsonb   | Triage Stage:  Accepted
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+

Comment (by timgraham):

 You can probably copy the `Cast` function from Django master
 (03b6947728466e4e907487f30dd4dfec94a8eb2f) into your project.

--
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/068.7aa3de8142bddea3f3c0210ae17cb378%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+
 Reporter:  eoinmurray|Owner:  nobody
 Type:  Cleanup/optimization  |   Status:  new
Component:  Documentation |  Version:  master
 Severity:  Normal|   Resolution:
 Keywords:  postgres, jsonb   | Triage Stage:  Accepted
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+

Comment (by eoinmurray):

 Is there any way to do this in Django <=1.9.5?

--
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/068.3d67e5b388935d232d93b16734ec71c9%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+
 Reporter:  eoinmurray|Owner:  nobody
 Type:  Cleanup/optimization  |   Status:  new
Component:  Documentation |  Version:  master
 Severity:  Normal|   Resolution:
 Keywords:  postgres, jsonb   | Triage Stage:  Accepted
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+
Changes (by claudep):

 * status:  closed => new
 * resolution:  worksforme =>
 * component:  contrib.postgres => Documentation
 * version:  1.9 => master
 * owner:   => nobody
 * type:  Bug => Cleanup/optimization
 * stage:  Unreviewed => Accepted


Comment:

 Thanks Simon for the details. Then it might be worth adding that hint
 somewhere in the documentation.

--
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/068.2e841ace721ead818f58ba7d67a9d367%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+--
 Reporter:  eoinmurray|Owner:
 Type:  Bug   |   Status:  closed
Component:  contrib.postgres  |  Version:  1.9
 Severity:  Normal|   Resolution:  worksforme
 Keywords:  postgres, jsonb   | Triage Stage:  Unreviewed
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+--

Comment (by charettes):

 As Claude pointed out the `textfield__contains` and `jsonfield__contains`
 lookups do not behave the same.

 In order to use the `textfield__contains` operator on `metadata ->
 'title'` field you'll have to cast your JSON field to text (just like you
 do in your `raw()` query) before hand.

 The following should work against the master branch (which introduced the
 `Cast` [https://docs.djangoproject.com/en/dev/ref/models/database-
 functions/#cast function]):

 {{{#!python
 from django.db import models

 File.objects.annotate(
 title=Cast('metadata__title', models.TextField()),
 ).filter(title__contains='moon')
 }}}

--
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/068.1927abb5d3a4af013148c372fe3129a4%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+--
 Reporter:  eoinmurray|Owner:
 Type:  Bug   |   Status:  closed
Component:  contrib.postgres  |  Version:  1.9
 Severity:  Normal|   Resolution:  worksforme
 Keywords:  postgres, jsonb   | Triage Stage:  Unreviewed
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+--

Comment (by eoinmurray):

 That will only return objects where the metadata contains the key 'title'
 with the exact value 'moon', it wont match with objects where 'title' is
 'the cow jumped over the moon'...

 I have just tested it there and using your suggested syntax doesnt work
 for me


 {{{
 File.objects.create(name="testfile.txt", owner=some_user,
 metadata={'title': 'the cow jumped over the moon'})

 print File.objects.filter(metadata__contains={'title': 'moon'})

 >>> []
 }}}

 The filter returns no items

--
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/068.776ab3ce6bc3425d98956ee83629bcff%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

Re: [Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+--
 Reporter:  eoinmurray|Owner:
 Type:  Bug   |   Status:  closed
Component:  contrib.postgres  |  Version:  1.9
 Severity:  Normal|   Resolution:  worksforme
 Keywords:  postgres, jsonb   | Triage Stage:  Unreviewed
Has patch:  0 |  Needs documentation:  0
  Needs tests:  0 |  Patch needs improvement:  0
Easy pickings:  0 |UI/UX:  0
--+--
Changes (by claudep):

 * status:  new => closed
 * needs_better_patch:   => 0
 * resolution:   => worksforme
 * needs_tests:   => 0
 * needs_docs:   => 0


Comment:

 Following
 https://docs.djangoproject.com/en/1.9/ref/contrib/postgres/fields
 /#containment-and-key-operations, then
 https://docs.djangoproject.com/en/1.9/ref/contrib/postgres/fields/#std
 :fieldlookup-hstorefield.contains, you should fine the proper containment
 syntax (`metadata__contains={'title': 'moon'}`).

--
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/068.f3fdaa4b825f9c999f61bd95850bc5c7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

[Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField

[Django]

#26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
--+-
 Reporter:  eoinmurray|  Owner:
 Type:  Bug   | Status:  new
Component:  contrib.postgres  |Version:  1.9
 Severity:  Normal|   Keywords:  postgres, jsonb
 Triage Stage:  Unreviewed|  Has patch:  0
Easy pickings:  0 |  UI/UX:  0
--+-
 I have a model called File with a JSONField. I create a new file and
 inside the JSONField is a key value pair

 {{{
 {"title": "the cow jumped over the moon"}
 }}}

 I want to be able to search for objects where the title contains moon...

 I can do it will the following raw SQL statement in Django


 {{{
 File.objects.raw('SELECT * FROM web_file WHERE (metadata ->> %s)::text
 LIKE %s;', ['title', '%moon%'])[0]
 }}}


 This works, but doesn't return a queryset, and doesn't play nice with the
 rest of my code. I was hoping I could do this query with the Django ORM.
 From the docs, I thought the following code would work:


 {{{
 File.objects.filter(metadata__title__contains='moon')
 }}}


 But gives the an error (traceback here
 https://gist.github.com/eoinmurray/04843b77e8d71fad8cfa4a990c00740a)


 {{{
 DataError: invalid input syntax for type json
 LINE 1: ...b_file" WHERE "web_file"."metadata" -> 'title' @> 'moon' ORD...

 DETAIL:  Token "moon" is invalid.
 CONTEXT:  JSON data, line 1: moon

 }}}

 Perhaps this is something that cannot be accomplished with the ORM, and
 therefore is not a bug - but I'm not really sure from the documentation...


 Here is all the code needed to recreate the error, Im using Django 1.9,
 Postgres 9.4 and psycopg2 2.6.1.

 models.py


 {{{
 from django.db import models
 from django.contrib.postgres.fields import JSONField

 class File(models.Model):
 owner = models.ForeignKey('auth.User', related_name='file_user')
 created_on = models.DateTimeField(auto_now_add=True, blank=True)
 name = models.CharField(max_length=100)
 metadata = JSONField(null=True, blank=True, default=dict())

 }}}

 query.py


 {{{
 from web.models import File
 from django.contrib.auth.models import User

 user = User.objects.get(pk=1)

 File.objects.create(name="testfile.txt", owner=user, metadata={'title':
 'the cow jumped over the moon'})

 # raw query that works
 # File.objects.raw('SELECT * FROM web_file WHERE (metadata ->> %s)::text
 LIKE %s;', ['title', '%moon%'])[0]

 File.objects.filter(metadata__title__contains='moon')

 >>> outputs traceback
 https://gist.github.com/eoinmurray/04843b77e8d71fad8cfa4a990c00740a
 >>> DataError: invalid input syntax for type json
 >>> LINE 1: ...b_file" WHERE "web_file"."metadata" -> 'title' @> 'moon'
 ORD...
  ^
 >>> DETAIL:  Token "moon" is invalid.
 >>> CONTEXT:  JSON data, line 1: moon
 }}}

--
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/053.77311ad6c64d1b3b26faff511fbebe1e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.