[Django]-How to write a query to get find value in a json field in django

by

16👍

✅

This usage is somewhat anti-pattern. Also, its implementation is not going to have regular performance, and perhaps is error-prone.
Normally don’t use jsonfield when you need to look up through fields. Use the way the RDBMS provides or MongoDB(which internally operates on faster BSON), as Daniel pointed out.

Due to the deterministic of JSON format,
you could achieve it by using contains (regex has issue when dealing w/ multiple '\' and even slower), I don’t think it’s good to use username in this way, so use name instead:

def make_cond(name, value):
    from django.utils import simplejson 
    cond = simplejson.dumps({name:value})[1:-1] # remove '{' and '}'
    return ' ' + cond # avoid '\"'

User.objects.get(jsonfield__contains=make_cond(name, value))

It works as long as

  • the jsonfield using the same dump utility (the simplejson here)
  • name and value are not too special (I don’t know any egde-case so far, maybe someone could point it out)
  • your jsonfield data is not corrupt (unlikely though)

Actually I’m working on a editable jsonfield and thinking about whether to support such operations. The negative proof is as said above, it feels like some black-magic, well.

👤okm

30👍

If you are using the django-jsonfield package, then this is simple. Say you have a model like this:

from jsonfield import JSONField
class User(models.Model):
    jsonfield = JSONField()

Then to search for records with a specific username, you can just do this:

User.objects.get(jsonfield__contains={'username':username})
👤bwv549

24👍

Since Django 1.9, you have been able to use PostgreSQL’s native JSONField. This makes search JSON very simple. In your example, this query would work:

User.objects.get(jsonfield__username='chingo')

If you have an older version of Django, or you are using the Django JSONField library for compatibility with MySQL or something similar, you can still perform your query.

In the latter situation, jsonfield will be stored as a text field and mapped to a dict when brought into Django. In the database, your data will be stored like this

{"username":"chingo","reputation":"5"}

Therefore, you can simply search the text. Your query in this siutation would be:

User.objects.get(jsonfield__contains='"username":"chingo"')
👤freethebees

18👍

2019: As @freethebees points out it’s now as simple as:

User.objects.get(jsonfield__username='chingo')

But as the doc examples mention you can query deeply, and if the json is an array you can use an integer to index it:

https://docs.djangoproject.com/en/2.2/ref/contrib/postgres/fields/#querying-jsonfield

>>> Dog.objects.create(name='Rufus', data={
...     'breed': 'labrador',
...     'owner': {
...         'name': 'Bob',
...         'other_pets': [{
...             'name': 'Fishy',
...         }],
...     },
... })
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': None})

>>> Dog.objects.filter(data__breed='collie')
<QuerySet [<Dog: Meg>]>

>>> Dog.objects.filter(data__owner__name='Bob')
<QuerySet [<Dog: Rufus>]>

>>> Dog.objects.filter(data__owner__other_pets__0__name='Fishy')
<QuerySet [<Dog: Rufus>]>

Although this is for postgres, I believe it works the same in other DBs like MySQL

👤Charles Thayer

1👍

If you use PostgreSQL you can use raw sql to solve problem.

username = 'chingo'
SQL_QUERY = "SELECT true FROM you_table WHERE jsonfield::json->>'username' = '%s'"
User.objects.extra(where=[SQL_EXCLUDE % username]).get()

where you_table is name of table in your database.

Any methods when you work with JSON like with plain text – looking like very bad way.
So, also I think that you need a better schema of database.

👤Shmele

1👍

Here is the way I have found out that will solve your problem:

search_filter = '"username":{0}'.format(username)
query = User.objects.get(jsonfield__contains=search_filter)

Hope this helps.

👤Hayk Davtyan

-3👍

You can’t do that. Use normal database fields for structured data, not JSON blobs.

If you need to search on JSON data, consider using a noSQL database like MongoDB.

👤Daniel Roseman

Leave a comment