JSON_PRETTY

Syntax

JSON_PRETTY(json_or_array)

Arguments

json_or_array : a valid JSON value or a JSON array.

Return Type

String that represents formatted JSON object or JSON array.

Remarks

Prints the elements in the same order as defined at the time of creation of the JSON object or JSON array.
Ignores whitespaces and newlines in the input value while printing the output.
Prints the keys in the JSON objects and table columns (returned when used with the TO_JSON function) in alphabetical order.
Each element of the JSON object or JSON array is printed on separate lines, indented by one level from its parent element. At each level of indentation, the element is printed with two leading spaces.
A JSON object is printed as a key/value pair where the value is separated from the key by a colon followed by a space (: ).
An empty JSON object or a JSON array is printed on a single line, without any spacing.

Examples

Example 1

JSON array of scalar values.

SQL

SELECT JSON_PRETTY('["G","alpha","20",10]');

+--------------------------------------+
| JSON_PRETTY('["G","alpha","20",10]') |
+--------------------------------------+
| [
  "G",
  "alpha",
  "20",
  10
]
|

Example 2

Using a JSON object.

SQL

SELECT JSON_PRETTY('{"position":1, "letter":    "alpha", "year":800}');

+--------------------------------------------------------------+
| JSON_PRETTY('{"position":1, "letter": "alpha", "year":800}') |
+--------------------------------------------------------------+
| {
  "letter": "alpha",
  "position": 1,
  "year": 800
} 
|

Example 3

Nested JSON arrays and JSON objects.

The whitespaces in the location input value are ignored in the output.

SQL

SELECT JSON_PRETTY('["alphabet",1,"Greek","alphabet",2,{"Coptic":"32"},{"shared_ltrs":["alpha","beta","gamma"]}]');

+-----------------------------------------------------------------------------------------------------------+
|JSON_PRETTY('["alphabet",1,"Greek","alphabet",2,{"Coptic":"2"},{"shared_ltrs":["alpha","beta","gamma"]}]') |
+-----------------------------------------------------------------------------------------------------------+
| [
  "alphabet",
  1,
  "Greek",
  "alphabet",
  2,
  {
    "Coptic": "2"
  },
  {
    "shared_ltrs": [
      "alpha",
      "beta",
      "gamma"
    ]
  }
]   
|

Example 4

Using a table called alphabet and defined as follows:

SQL

DESC j_alphabet;

+-----------+-------------+------+------+---------+-------+
| Field     | Type        | Null | Key  | Default | Extra |
+-----------+-------------+------+------+---------+-------+
| id        | varchar(6)  | YES  |      | NULL    |       |
| letter    | varchar(25) | YES  |      | NULL    |       |
| lang      | varchar(25) | YES  |      | NULL    |       |
| position  | int(11)     | YES  |      | NULL    |       |
+-----------+-------------+------+------+---------+-------+

Using the TO_JSON function will take a SQL value and return a JSON value instead.

SQL

SELECT JSON_PRETTY(TO_JSON(j_alphabet.*)) AS Data FROM j_alphabet;

+-----------------------+
| Data                  |
+-----------------------+
| {
 "id": "cop10",
 "lang": "Coptic",
 "letter": "yota",
 "position": 10
} |
| {
 "id": "grk03",
 "lang": "Greek",
 "letter": "gamma",
 "position": 3
}  |
| {
 "id": "grk02",
 "lang": "Greek",
 "letter": "beta",
 "position": 2
}   |
| {
 "id": "grk19",
 "lang": "Greek",
 "letter": "tau",
 "position": 19
}   |
| {
 "id": "grk04",
 "lang": "Greek",
 "letter": "delta",
 "position": 4
}  |
| {
 "id": "cop01",
 "lang": "Coptic",
 "letter": "alpha",
 "position": 1
} |
| {
 "id": "grk01",
 "lang": "Greek",
 "letter": "alpha",
 "position": 1
}  |
| {
 "id": "cop02",
 "lang": "Coptic",
 "letter": "beta",
 "position": 2
|

Example 5

Using a NULL value.

SQL

SELECT JSON_PRETTY(TO_JSON(NULL));

+----------------------------+
| JSON_PRETTY(TO_JSON(NULL)) |
+----------------------------+
| null                       |
+----------------------------+

Example 6

Using a boolean value.

SQL

SELECT JSON_PRETTY('{"valid":[true,false]}');

+------------------------------------------+
| JSON_PRETTY('{"valid":[true,false]}')    |
+------------------------------------------+
| {
  "valid": [
    true,
    false
  ]
} 
|