MySQL JSON Functions to Create Values

MySQL 5.7's new JSON data type has three functions to help you make sure your data is a valid utf8mb4 character set JSON document. They are JSON_ARRAY, JSON_QUOTE, and JSON_OBJECT. (You can also obtain JSON values by casting values of other types to the JSON type using CAST(value AS JSON)) What is the big difference? And when would you use one over another?

JSON_ARRAY takes a string as input and returns a JSON array with the values from the string.

mysql> select JSON_ARRAY('')
+----------------+
| JSON_ARRAY('') |
+----------------+
| [""]           |
+----------------+
1 row in set (0.01 sec)

mysql>  SELECT JSON_ARRAY('Foo', 42, now());
+-------------------------------------------+
| JSON_ARRAY('Foo', 42, now())              |
+-------------------------------------------+
| ["Foo", 42, "2016-02-15 07:31:56.000000"] |
+-------------------------------------------+

JSON_QUOTE takes the string given as input and wraps it with double quotes.

mysql> SELECT JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}');
+----------------------------------------+
| JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}') |
+----------------------------------------+
| "{foo,1,{0,1,2},\"FOOBAR\"}"           |
+----------------------------------------+
1 row in set (0.00 sec)

And finally JSON_OBJECT takes a list of key/value pairs and returns a JSON object containing those pairs. It will error if the number of arguments is odd or a key is named NULL.

 SELECT JSON_OBJECT('a',1,'bb',345);

+-----------------------------+
| JSON_OBJECT('a',1,'bb',345) |
+-----------------------------+
| {"a": 1, "bb": 345}         |
+-----------------------------+
1 row in set (0.00 sec)

So you would use JSON_OBJECT for creating JSON Objects (key/value pairs), JSON_ARRAY for creating arrays, some of which could be including in a bigger JSON Document, and JSON_QUOTE to escape embedded quotes. Next time we will look at the MySQL JSON Functions to modify JSON data.