Blog · Oracle

A Django JSON column on Oracle returns a LOB, not a string

A JSONField holds text, so a raw cursor looks like the quick way to read it. On Oracle that cursor returns a LOB object. The value is not a string, and json.loads() rejects it.

with connection.cursor() as cursor:
    cursor.execute("SELECT data FROM app_job WHERE ROWNUM = 1")
    value = cursor.fetchone()[0]

type(value)
# PostgreSQL: <class 'str'>
# Oracle:     <class 'oracledb.LOB'>

json.loads(value)
# TypeError: the JSON object must be str, bytes or bytearray, not LOB

Oracle stores a large text value as a Character Large Object. The driver does not transfer the content with the row. It returns a handle, and the content arrives at the first read. The handle behaves like a file, and it holds a cursor position.

Read the handle first

Call read() on the value to get the text. Accept both types, because the same code then runs on every database.

def as_text(value):
    if value is None or isinstance(value, str):
        return value
    if hasattr(value, "read"):          # Oracle LOB
        return value.read()
    if isinstance(value, (bytes, bytearray, memoryview)):
        return bytes(value).decode()
    return str(value)

json.loads(as_text(value))
# {'qty': 1}

The ORM does this already

Django's JSONField holds a converter for each backend. A query through the ORM returns the decoded object on Oracle and on PostgreSQL, with no extra code.

Job.objects.values_list("data", flat=True)[0]
# {'qty': 1} on every backend

Job.objects.filter(data__qty=1)          # also fine

Thus the fault appears only in code that goes around the ORM. Four common places are a raw cursor, a Model.objects.raw() call, a report command, and a data migration. The data migration is the most common of the four. A migration often uses SQL to keep away from the model classes.