SingleStore DB

JSON_PRETTY

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_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

SELECT JSON_PRETTY('["j","a","20",10]');
****
+----------------------------------+
| JSON_PRETTY('["j","a","20",10]') |
+----------------------------------+
| [
  "j",
  "a",
  "20",
  10
]   |

Example 2: JSON object

SELECT JSON_PRETTY('{"order":1, "location":    "Berlin", "amount":30.50}');
****
+---------------------------------------------------------------------+
| JSON_PRETTY('{"order":1, "location":    "Berlin", "amount":30.50}') |
+---------------------------------------------------------------------+
| {
  "amount": 30.50,
  "location": "Berlin",
  "order": 1
}         |

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

Example 3: Nested JSON arrays and JSON objects

SELECT JSON_PRETTY('["order",1,"cable","order",2,{"laptop":"10"},{"brand":["Dell","Apple","Acer"]}]');
****
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_PRETTY('["order",1,"cable","order",2,{"laptop":"10"},{"brand":["Dell","Apple","Acer"]}]')                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| [
  "order",
  1,
  "cable",
  "order",
  2,
  {
    "laptop": "10"
  },
  {
    "brand": [
      "Dell",
      "Apple",
      "Acer"
    ]
  }
] |

Example 4: With the TO_JSON function

Consider a Students table defined as follows:

DESCRIBE Students;
****
+-----------+----------+------+------+---------+-------+
| Field     | Type     | Null | Key  | Default | Extra |
+-----------+----------+------+------+---------+-------+
| ID        | int(11)  | YES  |      | NULL    |       |
| LASTNAME  | char(20) | YES  |      | NULL    |       |
| FIRSTNAME | char(20) | YES  |      | NULL    |       |
| LOCATION  | char(30) | YES  |      | NULL    |       |
+-----------+----------+------+------+---------+-------+

The JSON_PRETTY function prints the table columns in alphabetical order, when used with TO_JSON:

SELECT JSON_PRETTY(TO_JSON(Students.*)) AS Data FROM Students;
****
+------------------------------------------------------------------------------------------+
| Data                                                                                     |
+------------------------------------------------------------------------------------------+
| {
  "FIRSTNAME": "Wilman",
  "ID": 10,
  "LASTNAME": "Kala",
  "LOCATION": "Seattle"
}   |
| {
  "FIRSTNAME": "White",
  "ID": 20,
  "LASTNAME": "Clover",
  "LOCATION": "Helsinki"
} |
| {

Example 5: NULL value

SELECT JSON_PRETTY(TO_JSON(NULL));
****
+----------------------------+
| JSON_PRETTY(TO_JSON(NULL)) |
+----------------------------+
| null                       |
+----------------------------+

Example 6: Boolean value

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