New MySQL JSON Functions (more)

MySQL 8 is going to have new batch of JSON functions and last time JSON_PRETTY() was covered in some details. The recent release of 8.0.1 provides an opportunity to try these new functions and a few that might have been missed with 8.0.0.

Unquoting

The -> shortcut for JSON_EXTRACT() was introduced with MySQL 5.7. And now there is the unquoting extraction operator or ->> to simplify things again! Think of it as JSON_UNQUOTE wrapped around JSON EXTRACT. The following there queries produce the same output. mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(doc,"$.GNP")) FROM countryinfo WHERE _id = "USA"; +-----------------------------------------+ | JSON_UNQUOTE(JSON_EXTRACT(doc,"$.GNP")) | +-----------------------------------------+ | 8510700 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_UNQUOTE(doc->"$.GNP") FROM countryinfo WHERE _id = "USA"; +----------------------------+ | JSON_UNQUOTE(doc->"$.GNP") | +----------------------------+ | 8510700 | +----------------------------+ 1 row in set (0.00 sec) mysql> SELECT doc->>"$.GNP" FROM countryinfo WHERE _id = "USA"; +---------------+ | doc->>"$.GNP" | +---------------+ | 8510700 | +---------------+ 1 row in set (0.00 sec) mysql>

Aggregation

The new JSON_ARRAYAGG() and JSON_OBJECTAGG() takes a column or column argument and crates an array or object.

Clear as mud?

Well, examine this example: mysql> SELECT * FROM foo; +----------------------------------------+ | mycolumn | +----------------------------------------+ | {"key1": "value-A", "key2": "value-B"} | | {"key2": "value-X", "key3": "value-C"} | +----------------------------------------+ 2 rows in set (0.00 sec) mysql> SELECT JSON_ARRAYAGG(mycolumn) FROM foo; +----------------------------------------------------------------------------------+ | JSON_ARRAYAGG(mycolumn) | +----------------------------------------------------------------------------------+ | [{"key1": "value-A", "key2": "value-B"}, {"key2": "value-X", "key3": "value-C"}] | +----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

The two rows from table foo are combined to make a two element array. mysql> SELECT JSON_OBJECT(_id, doc->"$.GNP","Indep",doc->"$.IndepYear") FROM countryinfo LIMIT 2; +-----------------------------------------------------------+ | json_object(_id, doc->"$.GNP","Indep",doc->"$.IndepYear") | +-----------------------------------------------------------+ | {"ABW": 828, "Indep": null} | | {"AFG": 5976, "Indep": 1919} | +-----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>

The JSON_OBJECT() function takes pairs of columns, assumes they are a key/value pair, and combines them. Note that non-JSON columns and data from JSON columns can be combined, as well as literal strings.