I have a MySQL database with a table containing a JSON field called things. The JSON looks like this
things = {"value1": "phil", "value2": "jill"}
I have collection of objects that I have pulled from the database via
my_things = Name_table.objects.values
Now, I'd like to filter the my_things collection by one of the JSON fields. I've tried this
my_things = my_things.filter(things__contains={'value': 'phil'})
which returned an empty collection. I've also tried
my_things = my_things.filter(things={'value': 'phil'})
and
my_things = my_things.filter(things__exact={'value': 'phil'})
I'n using Django 1.10 and MySQL 5.7 Thoughts?
It depends on how exactly do you store JSON in field. If you use django-jsonfield, then your things will be string without spaces, with strings inside of quotation marks: '{"value1":"phil","value2":"jill"}'.
Then, via docs:
my_things = my_things.filter(things__contains='"value1":"phil"')
should return your filtered QuerySet, because
>>> tmp_str = '{"value1":"phil","value2":"jill"}'
>>> '"value1":"phil"' in tmp_str
True