[Django] #26511: Django 1.9 and Postgres 9.4 jsonb string containment inside JSONField
Django
----------------------------------+-----------------------------
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.
Django
Reporter: eoinmurray | Owner:
Type: Bug | Status: closed
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution: worksforme
Keywords: postgres, jsonb | Triage Stage: Unreviewed
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: <https://code.djangoproject.com/ticket/26511#comment:1>
Django
Reporter: eoinmurray | Owner:
Type: Bug | Status: closed
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution: worksforme
Keywords: postgres, jsonb | Triage Stage: Unreviewed
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: <https://code.djangoproject.com/ticket/26511#comment:2>
Django
Reporter: eoinmurray | Owner:
Type: Bug | Status: closed
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution: worksforme
Keywords: postgres, jsonb | Triage Stage: Unreviewed
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: <https://code.djangoproject.com/ticket/26511#comment:3>
Django
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
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: <https://code.djangoproject.com/ticket/26511#comment:4>
Django
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
Easy pickings: 0 | UI/UX: 0
Comment (by eoinmurray):
Is there any way to do this in Django <=1.9.5?
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:5>
Django
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
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: <https://code.djangoproject.com/ticket/26511#comment:6>
Django
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
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: <https://code.djangoproject.com/ticket/26511#comment:7>
Django
Reporter: eoinmurray | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Documentation | Version: master
Severity: Normal | Resolution:
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: <https://code.djangoproject.com/ticket/26511#comment:8>
Django
--------------------------------------+------------------------------------
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
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: 0 => 1
Comment:
Marking as "Patch needs improvement" per Simon's comment, "Searching a
whole JSON blob seems like a bad practice to me." The technique described
in comment:3 doesn't work, so it's possible some code changes might also
be needed to allow the desired behavior.
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:9>
Django
--------------------------------------+------------------------------------
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: 1
Comment (by charettes):
It looks like what we really need here is support for the `->>` operator
as `(lhs -> key)::text` is the JSON representation of the string
(`('"foo"'::jsonb)::text` yields `'"foo"'`).
I believe this should be done by introducing a new expression for this
purpose and documenting it.
In the meantime we could document using `Func('data',
template="%(expressions)s ->> 'hobby'", output_field=models.TextField())`.
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:10>
Django
--------------------------------------+------------------------------------
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: 1
Comment (by charettes):
Here's a [https://github.com/django/django/compare/master...charettes
:jsonb-fts POC] of what I had in mind.
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:11>
Django
--------------------------------------+------------------------------------
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: 1
Comment (by timgraham):
See #27205 for the same issue as it applies to `ArrayField`.
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:12>
Django
--------------------------------------+------------------------------------
Reporter: Eoin Murray | 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: 1
Comment (by felixxm):
I agree with Simon (see
[https://code.djangoproject.com/ticket/27824#comment:8 comment]). Both
`KeyTextTransform()` and `KeyTransform()` should be documented as a public
API, folks already use them.
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:13>
Django
Reporter: Eoin Murray | Owner:
Type: | AllenJonathan
Cleanup/optimization | Status: assigned
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: postgres, jsonb | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* owner: nobody => AllenJonathan
* needs_better_patch: 1 => 0
* status: new => assigned
Comment:
[https://github.com/django/django/pull/15956 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Eoin Murray | Owner:
Type: | AllenJonathan
Cleanup/optimization | Status: assigned
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: postgres, jsonb | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_better_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Eoin Murray | Owner:
Type: | AllenJonathan
Cleanup/optimization | Status: assigned
Component: Documentation | Version: dev
Severity: Normal | Resolution:
Keywords: postgres, jsonb | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by GitHub <noreply@…>):
In [changeset:"e9fd2b572410b1236da0d3d0933014138d89f44e" e9fd2b57]:
{{{
#!CommitTicketReference repository=""
revision="e9fd2b572410b1236da0d3d0933014138d89f44e"
Refs #26511 -- Fixed json.KeyTextTransform() on MySQL/MariaDB.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Eoin Murray | Owner:
Type: | AllenJonathan
Component: Documentation | Version: dev
Severity: Normal | Resolution: wontfix
Keywords: postgres, jsonb | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* status: assigned => closed
* needs_better_patch: 1 => 0
* has_patch: 1 => 0
* resolution: => wontfix
* stage: Accepted => Unreviewed
Comment:
Closing in favor of #33966.
There is no need to document `KeyTransform()` as the same can be achieved
with `F()` so there is no need to expose an extra API. Secondly, #33966
proposed adding `KT()` which is more handy and will be documented instead
of `KeyTextTransform()`.
--
Ticket URL: <https://code.djangoproject.com/ticket/26511#comment:17>