How the JSON_DETAILED() function works in Mariadb?

The JSON_DETAILED() function is a JSON function that adds whitespace and other formatting characters to a JSON document.

Posted on

The JSON_DETAILED() function is a JSON function that 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. The function can improve the appearance of the JSON document and make it easier to debug and understand.

Syntax

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

JSON_DETAILED(json_doc)

Where json_doc is a valid JSON document.

Examples

Example 1: Adding formatting to a JSON document with an array

In this example, we create a JSON document that contains an array of numbers using the JSON_ARRAY() function. Then we use the JSON_DETAILED() function to add formatting to the JSON document and make it more readable.

SELECT JSON_DETAILED(JSON_ARRAY(1, 2, 3)) AS result;

The output is:

+-------------------------+
| result                  |
+-------------------------+
| [
    1,
    2,
    3
] |
+-------------------------+

This means that the function returns a detailed JSON document that has whitespace characters, such as spaces, tabs, and newlines, to separate the elements in the array.

Example 2: Adding formatting to a JSON document with an object

In this example, we create a JSON document that contains an object with some key-value pairs using the JSON_OBJECT() function. Then we use the JSON_DETAILED() function to add formatting to the JSON document and make it more readable.

SELECT JSON_DETAILED(JSON_OBJECT('name', 'Alice', 'age', 25)) AS result;

The output is:

+----------------------------------------+
| result                                 |
+----------------------------------------+
| {
    "name": "Alice",
    "age": 25
} |
+----------------------------------------+

This means that the function returns a detailed JSON document that has whitespace characters, such as spaces, tabs, and newlines, to separate the key-value pairs in the object.

Example 3: Adding formatting to a JSON document with a nested array

In this example, we create a JSON document that contains a nested array using the JSON_ARRAY() function. Then we use the JSON_DETAILED() function to add formatting to the JSON document and make it more readable.

SELECT JSON_DETAILED(JSON_ARRAY(1, JSON_ARRAY(2, 3), 4)) AS result;

The output is:

+----------------------------------------------------+
| result                                             |
+----------------------------------------------------+
| [
    1,
    [
        2,
        3
    ],
    4
] |
+----------------------------------------------------+

This means that the function returns a detailed JSON document that has whitespace characters, such as spaces, tabs, and newlines, to separate the elements in the nested array.

Example 4: Adding formatting to a JSON document with a nested object

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_DETAILED() function to add formatting to the JSON document and make it more readable.

SELECT JSON_DETAILED(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 detailed JSON document that has whitespace characters, such as spaces, tabs, and newlines, to separate the key-value pairs in the nested object.

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

  • JSON_COMPACT(): This function 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.
  • JSON_PRETTY(): This function adds whitespace and indentation to a JSON document. The function takes a JSON document as an argument and returns a pretty JSON document that is more readable and human-friendly. The function can improve the appearance of the JSON document and make it easier to debug and understand.
  • JSON_QUOTE(): This function adds double quotes to a JSON value. The function takes a JSON value as an argument and returns a JSON value that is enclosed in double quotes. The function can escape any special characters, such as backslashes, newlines, or double quotes, within the JSON value.

Conclusion

The JSON_DETAILED() function is a useful JSON function that can add 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. The function can improve the appearance of the JSON document and make it easier to debug and understand. There are also some other related functions that can remove, add, or modify the formatting of JSON documents, such as JSON_COMPACT(), JSON_PRETTY(), and JSON_QUOTE().