How to make queries using Mysql JSON field and django 2

[Mark Phillips]

I have a model Document that has a json field "metadata" containing a dictionary of different values. One example:

{"Orientation": "Landscape", 

"Address": "Bethany Home Road", 

"Photo Type": "Many People", 

"a boolean": false, 

"Person": ["Sam Smith", "Hank Jones"], 

"Date": "05/13/2018", 

"Location": "Washington DC", 

"Decade": "2020"}

I can write a query to find all the record(s) in the Document table that the "Person" field contains "Sam Smith" like this

Document.objects.filter(metadata__Person__contains='Sam Smith')

Is there a way to write a query to find all the Documents that have "Sam*" in the metadata field? 

For example, if there were another document that had "Sammy Jacobs" in the "Person" field, the query would return both that record and the record with the "Sam Smith" in the Person field. 

Either some sort of "or" condition within the query that would allow me to search for "Sam Smith" or "Sammy Jacobs". Or, some sort of regex that I can put in for the search criteria that says search for all the "Sam*" occurrences in the "Person" list.

I have been googling for a solution, but have not found one yet.

Thanks!

Mark

 

[Jason]

the default jsonfield field type for django is postgres only, but there's support for mysql 5.7 via a third party package https://github.com/adamchainz/django-mysql.  specifically https://django-mysql.readthedocs.io/en/latest/model_fields/json_field.html