53

I'm using PostgreSQL and this new field from Django 1.9, JSONField. So I got the following data:

id|data
1 |[{'animal': 'cat', 'name': 'tom'}, {'animal': 'dog', 'name': 'jerry'}, {'animal': 'dog', 'name': 'garfield'}]

I'm trying to figure out how to filter in this list of json.

I tried something like object.filter(data__contains={'animal': 'cat'} but I know this is not the way.

Also I've been thinking in get this value and filter it in my code:

[x for x in data if x['animal'] == 'cat']
ivanleoncz
  • 9,070
  • 7
  • 57
  • 49
ezdookie
  • 1,477
  • 3
  • 15
  • 19
  • Did you read the comprehensive documentation on [querying JSONField](https://docs.djangoproject.com/en/1.9/ref/contrib/postgres/fields/#querying-jsonfield)? What is unclear? – Daniel Roseman Apr 03 '16 at 20:34
  • 2
    The above link doesn't work anymore. [Querying JSONfield now lives here](https://docs.djangoproject.com/en/3.2/topics/db/queries/#querying-jsonfield). – brunoparga Jun 10 '21 at 12:27

1 Answers1

121

As per the Django JSONField docs, it explains that that the data structure matches python native format, with a slightly different approach when querying.

If you know the structure of the JSON, you can also filter on keys as if they were related fields:

object.filter(data__animal='cat')
object.filter(data__name='tom')

By array access:

object.filter(data__0__animal='cat')

Your contains example is almost correct, but your data is in a list and requires:

object.filter(data__contains=[{'animal': 'cat'}])
Evan Byrne
  • 1,105
  • 1
  • 12
  • 17
Airith
  • 2,024
  • 1
  • 14
  • 10
  • 2
    that's it, thanks!! just one more thing, am I able to filter dates? Instead of 'animal' I got a key called 'start_date' which contains a date in ISO 8601 format – ezdookie Apr 03 '16 at 21:03
  • I don't think you can as they're only stored as strings. I think you'd have to pull out all the dates and then compare them in python. If it was possible you can test it by doing this comparison: `data__datetime__lte=other_datetime` or any of the other query shortcuts. – Airith Apr 04 '16 at 02:59
  • 3
    yes, it works... but only accessing an element array `data__0__datetime__lte='2015-03-12'`. do you have in mind any other way to search in the whole list? thank you! – ezdookie Apr 04 '16 at 04:37
  • Is there any way to apply a date filter to a JSON field? I tried `data__time__date_lte=date.today()`. But I am getting an error `Failed to handle exception Object of type Timestamp is not JSON serializable`. – Amar Prakash Pandey May 04 '21 at 09:39
  • nice very deep understanding of the feature! – adir abargil Jun 16 '22 at 09:22