`json` columns on MariaDB: insert fails with TypeError, fetch returns raw strings

[esutlie]

MariaDB aliases json columns to longtext internally. When DataJoint reads the column type back from information_schema, it sees longtext instead of json, so attr.json ends up False. That means json.dumps() is skipped on insert (raw dict hits pymysql → TypeError) and json.loads() is skipped on fetch (you get back a string instead of a dict).

The root cause is in heading.py — _init_from_database() matches the reported type against TYPE_PATTERN["JSON"] (r"jsonb?$"), which matches json but not longtext. So on MariaDB, any column declared as json silently loses its json behavior.

This hits two code paths:

• Insert (table.py): the elif attr.json: value = json.dumps(value) branch is skipped
• Fetch (codecs.py): the if attr.json: branch is skipped

Codec columns are partially unaffected on fetch because that path uses final_dtype from get_dtype() rather than attr.json.

Reproduction

import datajoint as dj

schema = dj.Schema("test_json_compat")

@schema
class JsonTest(dj.Manual):
    definition = """
    id : int
    ---
    metadata : json
    """

JsonTest.insert1({"id": 1, "metadata": {"key": "value"}})
# TypeError: dict can not be used as parameter

Suggested fix

In heading.py, _init_from_database() currently sets attr.json based solely on what the database reports. On MariaDB this is longtext, so the match fails. But the original declared type (json) is available from the table definition. If _init_from_database() cross-referenced the declared type against the reported type, it could detect this aliasing and set attr.json = True even when the DB reports longtext.

[esutlie]

A more targeted fix would be in the category handling block of _init_from_database(). The :json: comment marker is already parsed and resolved to category = "JSON", but only UUID is handled:

if category == "UUID":
    attr["uuid"] = True
elif category in CORE_TYPE_NAMES:
    pass

Adding the JSON case here (mirroring UUID) would restore attr.json = True regardless of what the database reports:

if category == "UUID":
    attr["uuid"] = True
elif category == "JSON":
    attr["json"] = True
elif category in CORE_TYPE_NAMES:
    pass

This is around line 508 in the current code.