Storing JSON strings in MariaDB

11 Mar 2021 - by 'Maurits van der Schee'

In SQL every column has a name, a type and a single value (that may be NULL in some cases). These strong guarantees makes working with SQL very nice. Nevertheless people seem intrigued by these "limitations" and propose "improvements". Some of these improvements are:

The ARRAY type in SQL99 and nested arrays in SQL2003.
The JSON type in MySQL
the JSON/JSONB type in PostgreSQL.
The XML type in SQL Server.

They all weaken the guarantee that a table row has a fixed number of keys and that every key has exactly one value and that each value has a specified type.

Sometimes it may be useful to store unstructured data. MariaDB takes the (sensible) approach of storing unstructured data in a LONGTEXT field (it's JSON type is an alias for LONGTEXT). But when you are creating an API, it would be great when that unstructured data is not double JSON encoded.

I have built JSON middleware in PHP-CRUD-API (an "automatic" API) to avoid double JSON encoding of unstructured fields. Below you see an example of reading a product record having an unstructured field named "properties" (with actual type LONGTEXT).

Without JSON middleware the output will be:

{
    "id": 1,
    "name": "Calculator",
    "price": "23.01",
    "properties": "{\"depth\":false,\"model\":\"TRX-120\",\"width\":100,\"height\":null}",
}

With JSON middleware the output will be:

{
    "id": 1,
    "name": "Calculator",
    "price": "23.01",
    "properties": {
        "depth": false,
        "model": "TRX-120",
        "width": 100,
        "height": null
    },
}

This does not only work when reading, but also when writing data to the API.

Conclusion

After evaluating a lot of directions of different relational databases to weaken it's structure guarantees I came across the sensible approach of MariaDB. I realized that unstructured data storage as a JSON encoded string is a great solution, but that the API would require a cosmetic change to the data (no double encoding).

Unfortunately MariaDB stores "LONGTEXT" instead of it's specified "JSON" alias in the reflection tables. This is why we still need to configure which string fields should be scanned to detect encoded JSON. If anyone knows how to find the specified JSON type from the reflection, please let me know!

Happy coding!

Links

PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.

TQ
dev.com