MySQL JSON_PRETTY

In MySQL, the JSON_PRETTY function is used to format JSON values in a human-readable manner, making them easier to interpret and debug. It applies indentation and newline characters to the JSON structure, enhancing its readability compared to the compact, machine-readable format.

Syntax

JSON_PRETTY(json_val)

Arguments:

json_val: A JSON value or a string representation of a JSON value.

The JSON_PRETTY function returns the formatted JSON value, which adheres to the following formatting rules:

Each array element or object member appears on a separate line, indented by one additional level compared to its parent.

Each indentation level adds two leading spaces.

A comma separating individual array elements or object members is printed before the newline that separates the two elements or members.

The key and the value of an object member are separated by a colon followed by a space (‘:’).

An empty object or array is printed on a single line.

No space is printed between the opening and closing brace.

Example

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

This query will return the following formatted JSON value:

[
  {
    "0": 1
  },
  {
    "1": 2
  },
  {
    "2": 3
  }
]

Here’s another example:

SELECT JSON_PRETTY('{ "name": "John Doe", "age": 30, "city": "New York" }');

This query will return the following formatted JSON value:

{
  "name": "John Doe",
  "age": 30,
  "city": "New York"
}

In summary, the JSON_PRETTY function plays a pivotal role in enhancing the readability and comprehension of JSON data in MySQL applications. Its ability to transform complex JSON structures into human-readable formats makes it a valuable tool for developers, analysts, and end-users alike.