MySQL JSON_PRETTY() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_PRETTY() function to format JSON data for better readability.

Introduction to MySQL JSON_PRETTY() function

The JSON_PRETTY() function is used to format JSON data for better readability. It makes the output of JSON data more human-friendly by adding indentation, newlines, and proper formatting.

Here’s the syntax of the JSON_PRETTY() function:

JSON_PRETTY(json_val)Code language: SQL (Structured Query Language) (sql)

In this syntax:

json_val: This is the JSON value that you want to pretty-print. It can be a valid JSON value or a string representation of a JSON value.

The function returns NULL if the json_val is NULL. If the json_value is not a valid JSON document, the function will fail with an error.

The JSON_PRETTY() function formats JSON data according to the following rules:

Every array element or object member is displayed on a new line, indented one level deeper than its parent.
For each level of indentation, two leading spaces are added.
A comma, which separates individual array elements or object members, precedes the new line separating them.
Object member keys and values are separated by a colon followed by a space (': ').
An empty object or array is represented on a single line without spaces between the opening and closing braces.
Special characters in string scalars and key names are escaped by the rules used by the JSON_QUOTE() function.

MySQL JSON_PRETTY() function examples

Let’s take some examples of using the JSON_PRETTY() function.

1) Formatting a scalar value

The following example uses the JSON_PRETTY() function to format a scalar value 1:

SELECT JSON_PRETTY('1');Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| JSON_PRETTY('1') |
+------------------+
| 1                |
+------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using MySQL JSON_PRETTY() function to pretty-print an array

The following example uses the JSON_PRETTY() function to format a JSON array of strings:

SELECT 
  JSON_PRETTY('["apple","orange","banana"]') result;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------+
| result                                |
+---------------------------------------+
| [
  "apple",
  "orange",
  "banana"
] |
+---------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Using MySQL JSON_PRETTY() function to pretty-print an object

The following example uses the JSON_PRETTY() function to format an object:

SELECT 
  JSON_PRETTY('{"name": "John", "age": 22, "job": "MySQL DBA"}') resultCode language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------------------+
| result                                                  |
+---------------------------------------------------------+
| {
  "age": 22,
  "job": "MySQL DBA",
  "name": "John"
} |
+---------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Pretty-printing a complex JSON document

The following example uses the JSON_PRETTY() function to format an object that includes a member which is an array:

SELECT 
  JSON_PRETTY(
    '{"name": "John", "age": 22, "job": "MySQL DBA", "skills": ["Linux","SQL"]}'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------------------------------------------------------------------------+
| result                                                                                            |
+---------------------------------------------------------------------------------------------------+
| {
  "age": 22,
  "job": "MySQL DBA",
  "name": "John",
  "skills": [
    "Linux",
    "SQL"
  ]
} |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the JSON_PRETTY() function to format JSON data for better readability.

Was this tutorial helpful?