tldr; here’s a small working example of this: https://github.com/EnriqueSoria/DisplayJsonValueInAdmin

Let’s say that, for some reason, we have a json stored in a TextField or a JSONField (available since Django 3.1) that stores a payload.

class Webhook(models.Model):
    payload = models.TextField()  # works also with models.JSONField()
    ... # more fields

Showing the timestamp as a datetime in the admin list

If we wanted to display the timestamp in the admin list, we could use some of the methods present in this guide. For instance, we could create a method in the admin:

@admin.register(Webhook)
class WebhookAdmin(admin.ModelAdmin):
    list_display = ("id", "payload_timestamp")

    def payload_timestamp(self, obj) -> datetime.datetime:
        payload_as_dict = json.loads(obj.payload)
        return datetime.datetime.fromisoformat(payload_as_dict["timestamp"])

Annotating the timestamp to allow sorting in the admin list

But, what happened if we wanted to enable sorting on this calculated field? Then we would have to annotate that field in the queryset. Here it comes the tricky part!

To extract a value from a json in MySQL we have the JSON_EXTRACT function, which is not implemented by the Django ORM, but can be used with Func:

@admin.register(Webhook)
class WebhookAdmin(admin.ModelAdmin):
    list_display = ("id", "payload_timestamp")

    @admin.display(description="Timestamp", ordering="_payload_timestamp")
    def payload_timestamp(self, obj):
        return obj._payload_timestamp

    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        return queryset.annotate(
            _payload_timestamp=Func(
                "payload",
                Value("$.timestamp"),
                function="JSON_EXTRACT",
                output_field=CharField(),
            ),
        )

…or, using django-mysql’s JSONExtract:

from django_mysql.models.functions import JSONExtract

queryset.annotate(
    _payload_timestamp=JSONExtract("payload", "$.timestamp"),
)

…as we can see, that could do the trick, but we can go further and try to unquote the timestamp and convert it to a datetime.

Casting the annotated timestamp as a DateTimeField

Before casting the value to a DateTimeField(), we need to unquote it. To do this we can leverage the JSON_UNQUOTE MySQL function and the ORM-provided Cast function. (unfortunately, we don’t have JSON_UNQUOTE implemented in django-mysql)

@admin.register(Webhook)
class WebhookAdmin(admin.ModelAdmin):
    list_display = ("id", "payload_timestamp")

    @admin.display(description="Timestamp", ordering="_payload_timestamp")
    def payload_timestamp(self, obj) -> datetime.datetime:
        return obj._payload_timestamp

    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        extracted_timestamp = Func(
            "payload",
            Value("$.timestamp"),
            function="JSON_EXTRACT",
            output_field=CharField(),
        )
        unquoted_timestamp = Func(
            extracted_timestamp,
            function="JSON_UNQUOTE",
        )
        cast_to_datetime_timestamp = Cast(
            unquoted_timestamp,
            output_field=DateTimeField(),
        )
        return queryset.annotate(_payload_timestamp=cast_to_datetime_timestamp)

Now we could even use this annotated _payload_timestamp to filter the queryset or annotate derivated values (i.e. .annotate(year=_payload_timestamp__year)), but unfortunately we can’t use it as date_hierarchy or list_fields in the admin.

Encapsulating this functions into a single one: JSONExtractAndCast

We can put all of this together into a single, reusable, piece of code:

…and our admin code would look like this:

@admin.register(Webhook)
class WebhookAdmin(admin.ModelAdmin):
    list_display = ("id", "payload_timestamp")

    @admin.display(description="Timestamp", ordering="_payload_timestamp")
    def payload_timestamp(self, obj) -> datetime.datetime:
        return obj._payload_timestamp

    def get_queryset(self, request):
        queryset = super().get_queryset(request)
        return queryset.annotate(
            _payload_timestamp=JSONExtractAndCast(
                "payload",
                "$.timestamp",
                output_field=DateTimeField(),
            )
        )