WL#9191: Add JSON_PRETTY function

User Feedback from presenting JSON features has suggested that we are missing a function to format JSON in a human-readable way (with new lines and indentation).

This functionality is available in both PHP and PostgreSQL under the name "pretty":

PHP: <?php echo json_encode($json, JSON_PRETTY_PRINT); ?>

PG:

 jsonb_pretty('[{"f1":1,"f2":null},2,null,3]')	
 —> 
 [
     {
         "f1": 1,
         "f2": null
     },
     2,
     null,
     3
 ]

http://www.postgresql.org/docs/9.5/static/functions-json.html

This WL is to implement a JSON_PRETTY function in MySQL:

• Similar to other JSON functions it should accept either a JSON native data-type or string representation of JSON
• It should return a JSON formatted string.

F1 - Provide a function that formats JSON data in a human-readable way.

F2 - The formatted data should be valid JSON text.

We will add the following function:

JSON_PRETTY()

This function converts a JSON document to a JSON text which represents the same document, with indentation and newlines added for readability.

TEXT JSON_PRETTY(JSON doc)
TEXT JSON_PRETTY(TEXT doc)

Returns:

• null - Returns NULL if doc is NULL.
• text - Returns prettified JSON text representation of doc if doc is not NULL. The character set of the returned string is utf8mb4.

Raises an error if doc is a string which is not a valid JSON text.

• Each array element or object member appears on a separate line, indented one extra level compared to its parent.
• Each indentation level adds two spaces of indentation.
• Commas that separate individual array elements or object members, should appear before the newline that separates the two elements/members.
• The key and the value of an object member are separated by a colon and a space character.
• Empty objects and arrays are on a single line with no space between the opening and closing brace.
• Special characters in string scalars and key names are escaped using the same rules as JSON_QUOTE, specified in WL#7909.

(Using two blanks for each indentation level is consistent with how MySQL currently outputs JSON from EXPLAIN and from the optimizer trace. The pretty-printers in PHP and PostgreSQL use four blanks.)

For example:

# returns NULL
SELECT JSON_PRETTY(NULL);

# raises an error
SELECT JSON_PRETTY('this is not JSON');

# returns {}
SELECT JSON_PRETTY('{}');

# returns []
SELECT JSON_PRETTY('[]');

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

# returns
# {
#   "a": 1,
#   "b": 2
# }
SELECT JSON_PRETTY('{"a":1,"b":2}');

# returns
# {
#   "a": [
#     1,
#     {}
#   ],
#   "b": []
# }
SELECT JSON_PRETTY('{"a":[1,{}],"b":[]}');

# returns
# [
#   "abc\ndef",
#   "\"abc\""
# ]
SELECT JSON_PRETTY(JSON_ARRAY('abc
def', '"abc"'));

• sql/json_dom.h, sql/json_dom.cc:
  • Add new function Json_wrapper::to_pretty_string(), which transforms the document represented by the Json_wrapper into human-readable form. It uses the existing wrapper_to_string() function, which already does the heavy lifting for Json_wrapper::to_string()
  • Add a new "pretty" argument to the wrapper_to_string() function, so that it can be used by both Json_wrapper::to_string() and Json_wrapper::to_pretty_string().

• sql/item_json_func.h, sql/item_json_func.cc:
  • Add new class Item_func_json_pretty, subclass of Item_str_func, which represents the JSON_PRETTY function.
  • Item_func_json_pretty::val_str() uses Json_wrapper::to_pretty_string() to format the returned string.

• sql/item_create.cc:
  • Add new class Create_func_json_pretty and wire it into the array of native functions to create at server startup.