how to write a query to get find value in a json field in django

django, django-models, django-queryset

Solution

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.

Problem

I have a json field in my database which is like ``` jsonfield = {'username':'chingo','reputation':'5'} ``` how can i write a query so that i can find if a user name exists. something like ``` username = 'chingo' query = User.objects.get(jsonfield['username']=username) ``` I know the above query is a wrong but I wanted to know if there is a way to access it?

Original source