How the JSON_PRETTY() function works in Mariadb?

The JSON_PRETTY() function is a built-in function in Mariadb that allows you to add whitespace and indentation to a JSON document, making it more readable and human-friendly. It can be useful when you want to display or debug JSON data in a formatted way.

Syntax

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

JSON_PRETTY(json_doc)

The function takes one argument, as follows:

• json_doc: This is the JSON document to format. It must be a valid JSON document or a column that contains JSON documents.

The function returns a new JSON document that is formatted with whitespace and indentation.

Examples

Example 1: Formatting a JSON array

In this example, we use the JSON_PRETTY() function to format a JSON array.

SELECT JSON_PRETTY(
  '[1, 2, 3]'
) AS formatted_doc;

The output is:

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

As you can see, the function returns a JSON array that is formatted with a new line and two spaces for each element.

Example 2: Formatting a JSON object

In this example, we use the JSON_PRETTY() function to format a JSON object.

SELECT JSON_PRETTY(
  '{"name": "Alice", "age": 25, "gender": "female"}'
) AS formatted_doc;

The output is:

+----------------------------------------------------------------+
| formatted_doc                                                  |
+----------------------------------------------------------------+
| {
    "name": "Alice",
    "age": 25,
    "gender": "female"
} |
+----------------------------------------------------------------+

As you can see, the function returns a JSON object that is formatted with a new line and two spaces for each key-value pair.

Example 3: Formatting a JSON document with nested structures

In this example, we use the JSON_PRETTY() function to format a JSON document with nested structures.

SELECT JSON_PRETTY(
  '{"name": "Alice", "age": 25, "gender": "female", "hobbies": ["reading", "writing", "coding"], "friends": [{"name": "Bob", "age": 30, "gender": "male"}, {"name": "Carol", "age": 28, "gender": "female"}]}'
) AS formatted_doc;

The output is:

+-----------------------------------------------+
| formatted_doc                                 |
+-----------------------------------------------+
| {
    "name": "Alice",
    "age": 25,
    "gender": "female",
    "hobbies":
    [
        "reading",
        "writing",
        "coding"
    ],
    "friends":
    [
        {
            "name": "Bob",
            "age": 30,
            "gender": "male"
        },
        {
            "name": "Carol",
            "age": 28,
            "gender": "female"
        }
    ]
} |
+-----------------------------------------------+

As you can see, the function returns a JSON document that is formatted with a new line and two spaces for each level of nesting.

Related Functions

There are some other functions in Mariadb that are related to the JSON_PRETTY() function. Here are some of them:

• JSON_COMPACT() function: This function does the opposite of the JSON_PRETTY() function. It removes whitespace and unnecessary characters from a JSON document, making it more compact and efficient to store or transmit. For example, JSON_COMPACT('[ 1, 2, 3 ]') returns [1,2,3].
• JSON_LENGTH() function: This function returns the length of a JSON document. It takes a JSON document and an optional path argument, and returns the number of elements in the JSON document at the specified path. For example, JSON_LENGTH('{"a": "b", "c": ["d", "e"]}', '$.c') returns 2.
• JSON_TYPE() function: This function returns the type of a JSON document. It takes a JSON document as an argument, and returns a string that indicates the type of the JSON document. For example, JSON_TYPE('{"a": "b", "c": ["d", "e"]}') returns OBJECT.

Conclusion

The JSON_PRETTY() function is a useful function in Mariadb that allows you to add whitespace and indentation to a JSON document, making it more readable and human-friendly. It can handle different types of JSON documents, such as arrays, objects, or scalars. It returns a new JSON document that is formatted with whitespace and indentation. There are also some other functions that are related to the JSON_PRETTY() function, such as JSON_COMPACT(), JSON_LENGTH(), and JSON_TYPE(). You can use these functions to manipulate JSON data in different ways.