JSON_
On this page
Also referred as "pretty printing", this function outputs the given JSON object or JSON array in an indented and legible format.
Syntax
JSON_PRETTY(json_or_array)Arguments
-
json_: a valid JSON value or a JSON array.or_ 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.
SELECT JSON_PRETTY('["G","alpha","20",10]');
+--------------------------------------+
| JSON_PRETTY('["G","alpha","20",10]') |
+--------------------------------------+
| [
"G",
"alpha",
"20",
10
]
|Example 2
Using a JSON object.
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.
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:
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_ function will take a SQL value and return a JSON value instead.
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.
SELECT JSON_PRETTY(TO_JSON(NULL));
+----------------------------+
| JSON_PRETTY(TO_JSON(NULL)) |
+----------------------------+
| null |
+----------------------------+Example 6
Using a boolean value.
SELECT JSON_PRETTY('{"valid":[true,false]}');
+------------------------------------------+
| JSON_PRETTY('{"valid":[true,false]}') |
+------------------------------------------+
| {
"valid": [
true,
false
]
}
|Last modified: March 7, 2023