[FIXED] Bad format after converting a JSONField to a TextField

Issue

I encountered an issue with a Django project where a JSONField was initially used to store data. To maintain the order of the data as it was saved, I implemented a sorting functionality. However, I found that this sorting operation, although necessary for display, seemed inefficient when the data was accessed.

To address this, I decided to convert the JSONField to a TextField and use json.dump to ensure the order is preserved without the need for sorting each time the data is accessed.

Previously, the field on the model was a JSONField as shown below

class UserProfile(models.Model):
    ...
    data = models.JSONField(default=dict, null=True)

I decided to migrate to a TextField, as shown below

class UserProfile(models.Model):
    ...
    data = models.TextField(blank=True, null=True)

Unfortunately, while some of the records were successfully converted to correct json format, the conversion process didn't work as expected. Django successfully converted some fields to the correct JSON string format, but others were not handled correctly. An example of the incorrect format is as follows:

'{'is_valid': True, 'name': 'John Doe'}'

As seen above, Django converted the data to a string, but boolean values, such as True, were not handled correctly.

I am seeking suggestions on how to address this issue. Currently, I am considering replacing the single quotes with double quotes and converting boolean values like this:

data = '{'is_valid': True, 'name': 'John Doe'}'
formatted_data = data.replace("'", '"').replace('True', 'true').replace('False', 'false')

However, I believe there might be a more elegant or efficient solution. I would appreciate any insights or recommendations. Thank you.

Solution

You can not put a dictionary in a TextField, or at least not without problems starting to occur. This is what happened.

Indeed, a TextField will happily accept any object, but call str(…) on it, and then we're screwed.

Indeed, {"a": "b"} in Python is will be converted with str(…) to "{'a': 'b'}", and that is what is saved. But it is not valid JSON.

However, I believe there might be a more elegant or efficient solution.

Your solution however has some caveats. You convert True to true, but imagine that the string is {'name': 'True foo'}, then now we have {'name': 'true foo'}, so we don't only convert booleans, but everywhere True pops up, even for a given name.

I think the first problem we have is to fix the data, and migrate it back to a JSONField. We can do that with:

import json
from ast import literal_eval

to_update = []
for profile in UserProfile.objects.all():
    try:
        json.loads(profile.data)
    except ValueError:
        profile.data = json.dumps(literal_eval(profile.data))
        to_update.append(profile)
UserProfile.objects.bulk_update(to_update, fields=('data',))

here we check if the data can be interpreted as JSON, and if not, we parse it as a Python literal, and rewrite it as a JSON blob.

Next we can make a custom field that does the loading and parsing for us, with:

from django.db.models.fields import TextField


class OrderedJSONField(TextField):
    def to_python(self, value):
        value = super().to_python(value)
        if isinstance(value, str):
            return json.loads(value)
        return value

    def get_prep_value(self, value):
        return json.dumps(value)

and then we can use that field to automatically wrap and unwrap data in a JSON blob:

class UserProfile(models.Model):
    # …
    data = models.OrderedJSONField(default=dict, null=True)

we essentially here take advantage of the fact that Python's JSON library retains order in both serializing and deserializing, and that since python-3.7, dictionaries are ordered.

But we lose a lot. Indeed, the reason the items returned unordered is because databases nowadays have support for JSON, they store objects in a more compact manner, and also made available tools to filter records by looking into the JSON blob, we will lose all that functionality. The question is whether the order of a JSON blob is that important to lose the information, especially since JavaScript does not always follows insertion order, and thus eventually it can all be effort for nothing.

Answered By - willeM_ Van Onsem