Inconsistent "contains" filter behaviour for jsonfield mysql/sqlite

Question

I'm getting inconsistent results depending on the backend database when using jsonfield "contains" queries.

from polymorphic.models import PolymorphicModel
import jsonfield

class Action(PolymorphicModel):
    new_object = jsonfield.JSONField(null=True)

In another model I filter on this jsonfield, but I get different results.

On sqlilite the following works:

    return Action.objects.filter(
            new_object__contains={"ref_id": self.id}
        ).order_by("-created_at")[:5]

On mysql I have to do the following:

    return Action.objects.filter(
            new_object__contains=f"\"ref_id\": {self.id}"
        ).order_by("-created_at")[:5]

So it seems to me in one environment it's deserialising the json into a dict, whereas the other keeps it as string..

Does anyone have a good way of handling this? Could there an issue with one of the configurations not lining up with the database?

Edit: This is Django 2.2

Since Django-3.1, on MySQL it is using a JSONField as well. In the early versions, a `JsonField` was indeed, on most databases just a `VARCHAR` with serialization/deserialization done with Django. — Willem Van Onsem, Dec 15 '20 at 11:31
I see, Yeah, I'm in a Django 2.2 project. Do you know a way to handle this gracefully, without having to change the code per environment? — Martijn, Dec 15 '20 at 12:49

0 Answers0