How to perform filtering with a Django JSONField?

Question

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']

Mulligrubs

Did you read the comprehensive documentation on querying JSONField? What is unclear? – Whimwham

The above link doesn't work anymore. Querying JSONfield now lives here. – Uriia

@Uriia Maybe you can find something useful here : docs.djangoproject.com/en/4.2/topics/db/queries/… – Mending

Answer 1

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'}])

Iodate

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 – Mulligrubs

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. – Iodate

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! – Mulligrubs

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. – Mot

nice very deep understanding of the feature! – Consultant

For me i had to switch from using sqllite3 to using postgres to get the behaviour i wanted... – Sandpit