#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: <https://code.djangoproject.com/ticket/26511>
Django <https://code.djangoproject.com/>
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 [email protected].
To post to this group, send email to [email protected].
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.
