In MySQL, the JSON_PRETTY() function provides pretty-printing of JSON values. It returns the JSON values in a nicely formatted way, which makes it easier for us humans to read.

You provide the JSON values as an argument to the function. The function then returns those values formatted according to its formatting rules.

Syntax

The syntax goes like this:

JSON_PRETTY(json_val)

Where json_val is the JSON value to format. This must be a JSON value or a valid string representation of a JSON value. If the value is not a JSON document, or if it cannot cannot be parsed as one, the function fails with an error.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT JSON_PRETTY('{"a": 1, "b": 2, "c": 3}') Result;

Result:

+----------------------------------+
| Result                           |
+----------------------------------+
| {
  "a": 1,
  "b": 2,
  "c": 3
} |
+----------------------------------+

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

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

Example 2 – Arrays

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

A comma separating individual array elements is printed before the newline that separates the two elements or members (same as with objects).

Here’s an example of formatting an array.

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

Result:

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

Here’s an example of two arrays, one nested inside the other.

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

Result:

+-------------------------------------------+
| Result                                    |
+-------------------------------------------+
| [
  1,
  2,
  [
    3,
    4,
    5
  ]
] |
+-------------------------------------------+

Example 3 – Empty Objects and Arrays

Empty objects and arrays are printed on a single line. No space is printed between the opening and closing brace.

SELECT JSON_PRETTY('[1, 2, [], {}]') Result;

Result:

+--------------------------+
| Result                   |
+--------------------------+
| [
  1,
  2,
  [],
  {}
] |
+--------------------------+

Example 4 – Whitespace

Extraneous whitespaces and newlines present in this value have no effect on the output.

SELECT JSON_PRETTY('[    1    ,  
 
2  ,   

3]') Result;

Result:

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

Example 5 – Indents

Each level of indentation adds two leading spaces.

SET @data = '{"Person": {"Name": "Bart", "Age": 10, "Friends": ["Bart", "Milhouse"]}}';
SELECT JSON_PRETTY(@data) Result;

Result:

{
  "Person": {
    "Age": 10,
    "Name": "Bart",
    "Friends": [
      "Bart",
      "Milhouse"
    ]
  }
}