MariaDB JSON_DETAILED() Explained

In MariaDB, JSON_DETAILED() is a built-in function that takes a JSON document, and returns it in a more human-readable format.

This is sometimes referred to as prettifying the document. It’s similar to MySQL’s JSON_PRETTY() function.

For the opposite effect (i.e. to condense a JSON document down), use the JSON_COMPACT() function.

Syntax

The syntax goes like this:

JSON_DETAILED(json_doc[, tab_size])

Where json_doc is the JSON document, and tab_size is an optional value that specifies the size of the tab/indents.

Example

Here’s an example to demonstrate.

SET @json_document = '{ "name": "Wag", "type": "Dog", "weight": 20 }';

SELECT JSON_DETAILED(@json_document);

Result:

+----------------------------------------+
| JSON_DETAILED(@json_document)          |
+----------------------------------------+
| {
    "name": "Wag",
    "type": "Dog",
    "weight": 20
} |
+----------------------------------------+

The original document is all on one line, with no tabs/indents or other formatting.

The result is spread across multiple lines and contains tabs/indents, which makes the document easier for us humans to read.

Nested Structures

Here are a couple more examples, this time with nested structures:

SET @json_document = '{ "_id" : 1, "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] }';

SELECT JSON_DETAILED(@json_document);

Result:

+---------------------------------------+
| JSON_DETAILED(@json_document)         |
+---------------------------------------+
| {
    "_id": 1,
    "awards": 
    [
        "Top Dog",
        "Best Dog",
        "Biggest Dog"
    ]
} |
+---------------------------------------+

And another:

SET @json_document = '{ "_id" : 2, "specs" : { "height" : 400, "weight" : 15, "color" : "brown" } }';

SELECT JSON_DETAILED(@json_document);

Result:

+---------------------------------------+
| JSON_DETAILED(@json_document)         |
+---------------------------------------+
| {
    "_id": 2,
    "specs": 
    {
        "height": 400,
        "weight": 15,
        "color": "brown"
    }
} |
+---------------------------------------+

Tab Size

You also have the option of specifying the tab size. To do this, pass the desired tab size as a second argument.

Example

SET @json_document = '{ "_id" : 1, "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] }';

SELECT JSON_DETAILED(@json_document, 1);

Result:

{
 "_id": 1,
 "awards": 
 [
  "Top Dog",
  "Best Dog",
  "Biggest Dog"
 ]
}

And here it is again, but with a larger tab size:

SET @json_document = '{ "_id" : 1, "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] }';

SELECT JSON_DETAILED(@json_document, 10);

Result:

{
        "_id": 1,
        "awards": 
        [
                "Top Dog",
                "Best Dog",
                "Biggest Dog"
        ]
}

Larger JSON Document

Here’s an example with a slightly larger JSON document.

SET @json_document = '{"_id":1,"name":"Wag","details":{"type":"Dog","weight":20,"awards":{"Florida Dog Awards":"Top Dog","New York Marathon":"Fastest Dog","Sumo 2020":"Biggest Dog"}}}';

SELECT JSON_DETAILED(@json_document);

Result:

{
    "_id": 1,
    "name": "Wag",
    "details": 
    {
        "type": "Dog",
        "weight": 20,
        "awards": 
        {
            "Florida Dog Awards": "Top Dog",
            "New York Marathon": "Fastest Dog",
            "Sumo 2020": "Biggest Dog"
        }
    }
}

Null Argument

If the argument is NULL, the result is NULL:

SELECT JSON_DETAILED(null);

Result:

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

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_DETAILED();

Result:

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

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

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

Result:

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