How the JSON_COMPACT() function works in Mariadb?

The JSON_COMPACT() function is a JSON function that removes whitespace and other unnecessary characters from a JSON document.

Posted on

The JSON_COMPACT() function is a JSON function that removes whitespace and other unnecessary characters from a JSON document. The function takes a JSON document as an argument and returns a compact JSON document that is equivalent to the original one. The function can reduce the size of the JSON document and make it more efficient to store and transmit.

Syntax

The syntax of the JSON_COMPACT() function is as follows:

JSON_COMPACT(json_doc)

Where json_doc is a valid JSON document.

Examples

Example 1: Compacting a JSON document with whitespace

In this example, we create a JSON document that contains some whitespace characters, such as spaces, tabs, and newlines, using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_COMPACT() function to compact the JSON document and remove the whitespace characters.

SELECT JSON_COMPACT(JSON_OBJECT('name', 'Alice',
                                'age', 25,
                                'hobbies', JSON_ARRAY('reading',
                                                      'writing',
                                                      'coding'))) AS result;

The output is:

+--------------------------------------------------------------------+
| result                                                             |
+--------------------------------------------------------------------+
| {"name":"Alice","age":25,"hobbies":["reading","writing","coding"]} |
+--------------------------------------------------------------------+

This means that the function returns a compact JSON document that has no whitespace characters.

Example 2: Compacting a JSON document with escape sequences

In this example, we create a JSON document that contains some escape sequences, such as \n for newline, \t for tab, and \" for double quote, using the JSON_QUOTE() function. Then we use the JSON_COMPACT() function to compact the JSON document and remove the escape sequences.

SELECT JSON_COMPACT(JSON_QUOTE('Hello\nWorld\t"!')) AS result;

The output is:

+---------------------+
| result              |
+---------------------+
| "Hello\nWorld\t\"!" |
+---------------------+

This means that the function returns a compact JSON document that has no escape sequences.

Example 3: Compacting a JSON document with redundant commas

In this example, we create a JSON document that contains some redundant commas, such as trailing commas in arrays and objects, using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_COMPACT() function to compact the JSON document and remove the redundant commas.

SELECT JSON_COMPACT(JSON_OBJECT('name', 'Bob',
                                'age', 30,
                                'hobbies', JSON_ARRAY('sports',
                                                      'music',
                                                      'movies'))) AS result;

The output is:

+---------------------------------------------------------------+
| result                                                        |
+---------------------------------------------------------------+
| {"name":"Bob","age":30,"hobbies":["sports","music","movies"]} |
+---------------------------------------------------------------+

This means that the function returns a compact JSON document that has no redundant commas.

There are some other JSON functions that are related to the JSON_COMPACT() function. Here are some of them:

  • JSON_DETAILED(): This function adds whitespace and other formatting characters to a JSON document. The function takes a JSON document as an argument and returns a detailed JSON document that is more readable and human-friendly.
  • JSON_LENGTH(): This function returns the length of a JSON document. The function takes a JSON document and an optional path as arguments and returns the number of elements in the JSON document or the JSON value at the specified path.

Conclusion

The JSON_COMPACT() function is a useful JSON function that can remove whitespace and other unnecessary characters from a JSON document. The function takes a JSON document as an argument and returns a compact JSON document that is equivalent to the original one. The function can reduce the size of the JSON document and make it more efficient to store and transmit. There are also some other related functions that can add formatting, measure length, or calculate storage size of JSON documents, such as JSON_DETAILED(), JSON_LENGTH(), and JSON_STORAGE_SIZE().