MariaDB JSON_DETAILED() and JSON_PRETTY()

MariaDB JSON_DETAILED() formats a JSON document with indentation and newlines for readability. In selected releases, JSON_PRETTY() is an alias for the same function. See MariaDB’s official JSON_DETAILED reference and JSON_PRETTY compatibility note.

JSON_PRETTY() is available as an alias in MariaDB 10.10.3, 10.9.5, 10.8.7, 10.7.8, 10.6.12, 10.5.19, and 10.4.28, and in later releases in those series. Use JSON_DETAILED() on older MariaDB versions. MySQL’s JSON_PRETTY() is a separate function with the same general formatting purpose.

JSON_COMPACT() is opposite to this function, it removes unnecessary spaces and newlines from JSON documents.

MariaDB JSON_DETAILED() Syntax

Here is the syntax of the MariaDB JSON_DETAILED() function:

JSON_DETAILED(json)
JSON_DETAILED(json, tab_size)
JSON_PRETTY(json)
JSON_PRETTY(json, tab_size)

Parameters

json

Required. The JSON document to be processed.

tab_size

Optional. It specifies the tab/indent size.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_DETAILED'.

Return value

MariaDB JSON_DETAILED() function add spaces and newlines to beautify a given JSON document.

If you provide a NULL value as an argument, JSON_DETAILED() will return NULL.

MariaDB JSON_DETAILED() Examples

The following example shows the usages of the MariaDB JSON_DETAILED() function.

Basic example

SET @json_doc = '{"x":1,"b": [2,  3]}';

SELECT JSON_DETAILED(@json_doc);

Output:

+------------------------------------------------------------+
| JSON_DETAILED(@json_doc)                                   |
+------------------------------------------------------------+
| {
    "x": 1,
    "b":
    [
        2,
        3
    ]
} |
+------------------------------------------------------------+

Indentation size

The MariaDB JSON_DETAILED() function allows you to vary the size of the indentation using the tab_size parameter:

SET @json_doc = '{"x":1,"b": [2,  3]}';

SELECT JSON_DETAILED(@json_doc, 1);

Output:

+------------------------------------+
| JSON_DETAILED(@json_doc, 1)        |
+------------------------------------+
| {
 "x": 1,
 "b":
 [
  2,
  3
 ]
} |
+------------------------------------+

NULL parameters

If you provide a NULL value as an argument, JSON_DETAILED() will return NULL.

SELECT JSON_DETAILED(NULL);

Output:

+---------------------+
| JSON_DETAILED(NULL) |
+---------------------+
| NULL                |
+---------------------+

Conclusion

In MariaDB, JSON_DETAILED() is a built-in function that formats the output of a JSON document so that it is easier to read.