id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
26511	Document KeyTextTransform() and KeyTransform().	Eoin Murray	AllenJonathan	"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
}}}"	Cleanup/optimization	closed	Documentation	dev	Normal	wontfix	postgres, jsonb	eoin@…	Unreviewed	0	0	0	0	0	0