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'