I have a model like this:
class MyModel(models.Model):
details = models.JSONField()
# other fields
I want to annotate some fields from this model like this:
qs = MyModel.objects.filter(id__in=given_list).annotate(
first_name=F('details__first_name'),
last_name=F('details__last_name')
)
However the F() expression is not considering the json keys, its just returning the details field only.
I am using MySQL, so cannot use KeyTextTransform.
I tried using RawSQL like this:
qs = MyModel.objects.filter(id__in=given_list).annotate(
first_name=RawSQL("(details->%s)", ('first_name',)),
last_name=RawSQL("(details->%s)", ('last_name',))
)
But it was giving this error:
MySQLdb._exceptions.OperationalError: (3143, 'Invalid JSON path expression. The error is around character position 1.')
So what can I do to make everything work as expected?
You can use JSONExtract, it will be easier to write and understand:
from django_mysql.models.functions import JSONExtract
qs = MyModel.objects.filter(id__in=given_list).annotate(
first_name=JSONExtract('details', '$.first_name'),
last_name=JSONExtract('details', '$.last_name')
)
MySQL json extractions have a special syntax using jsonfield->"$.key". Try with this:
qs = MyModel.objects.filter(id__in=given_list).annotate(
first_name=RawSQL("(details->%s)", ('$.first_name',)),
last_name=RawSQL("(details->%s)", ('$.last_name',))
)
You can just add properties to your MyModel and have them return the corresponding information
class MyModel(models.Model):
details = models.JSONField()
@property
def first_name(self):
return self.details['first_name']
@property
def last_name(self):
return self.details['last_name']