MariaDB JSON_LOOSE() Explained

In MariaDB, JSON_LOOSE() is a built-in function that adds spaces to a JSON document to make it more readable.

It’s similar to the JSON_DETAILED() function, except that it doesn’t emphasise nested structures in the way that JSON_DETAILED() does.

For the opposite effect (i.e. to remove unnecessary spaces), use the JSON_COMPACT() function.

Syntax

The syntax goes like this:

JSON_LOOSE(json_doc)

Where json_doc is the JSON document.

Example

Here’s an example to demonstrate.

SET @json = '{"name":"Wag","type":"Dog"}';

SELECT JSON_LOOSE(@json);

Result:

+--------------------------------+
| JSON_LOOSE(@json)              |
+--------------------------------+
| {"name": "Wag", "type": "Dog"} |
+--------------------------------+

The original document contains no spaces, but the result of JSON_LOOSE() contains spaces..

Here it is again compared with the original document:

SET @json = '{"name":"Wag","type":"Dog"}';

SELECT 
    @json AS 'Original',
    JSON_LOOSE(@json) AS 'Modified';

Result:

+-----------------------------+--------------------------------+
| Original                    | Modified                       |
+-----------------------------+--------------------------------+
| {"name":"Wag","type":"Dog"} | {"name": "Wag", "type": "Dog"} |
+-----------------------------+--------------------------------+

Objects and Arrays

Here’s an example with an object and an array, just to show that it doesn’t provide any extra formatting for such structures.

SET @json = '{"specs":{"w":10,"h":30},"scores":[1,2,3]}';

SELECT JSON_LOOSE(@json);

Result:

+----------------------------------------------------+
| JSON_LOOSE(@json)                                  |
+----------------------------------------------------+
| {"specs": {"w": 10, "h": 30}, "scores": [1, 2, 3]} |
+----------------------------------------------------+

If you need extra formatting for these structures, try the JSON_DETAILED() function.

Null Argument

If the argument is NULL, the result is NULL:

SELECT JSON_LOOSE(null);

Result:

+------------------+
| JSON_LOOSE(null) |
+------------------+
| NULL             |
+------------------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_LOOSE();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_LOOSE'

It’s the same when you provide too many arguments:

SELECT JSON_LOOSE('{"a":1}', 2);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_LOOSE'