In MariaDB, JSON_COMPACT() is a built-in function that removes all unnecessary spaces from a JSON document, so that it’s as short and compact as possible, and returns the result.

This can be handy for when you need to store JSON data in a database column, and you don’t want the JSON documents to use up any more space than is necessary.

For the opposite effect (i.e. to prettify a JSON document by adding indents and spreading it over multiple lines), use the JSON_DETAILED() function.

Syntax

The syntax goes like this:

JSON_COMPACT(json_doc)

Where json_doc is the JSON document.

Example

Here’s an example to demonstrate.

SET @json_document = '{"a": [0, 1], "b": [2, 3]}';

SELECT 
    JSON_COMPACT(@json_document) AS Compact,
    @json_document AS Original;

Result:

+-----------------------+----------------------------+
| Compact               | Original                   |
+-----------------------+----------------------------+
| {"a":[0,1],"b":[2,3]} | {"a": [0, 1], "b": [2, 3]} |
+-----------------------+----------------------------+

In this case, the first column contains the compacted version, and the second column contains the original JSON document before it was compacted.

Larger JSON Document

Here’s an example with a slightly larger, prettified 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_COMPACT(@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 Arguments

If the argument is NULL, the result is NULL:

SELECT JSON_COMPACT(null);

Result:

+--------------------+
| JSON_COMPACT(null) |
+--------------------+
| NULL               |
+--------------------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_COMPACT();

Result:

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

We get the same result if we provide too many arguments:

SELECT JSON_COMPACT('{ "a": 1}', '{ "b": 1 }');

Result:

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