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.

Pretty JSON with JSON_PRETTY()

JSON is a great way to share data and is highly praised in being human readable. Well, compared to XML and some other standards it is easier to read. But sometimes the data looks mushed or wilted. mysql> SELECT doc FROM countryinfo WHERE _id='USA'; ... {"GNP": 8510700, "_id": "USA", "Name": "United States", "IndepYear": 1776, "geography": {"Region": "North America", "Continent": "North America", "SurfaceArea": 9363520}, "government": {"HeadOfState": "George W. Bush", "GovernmentForm": "Federal Republic"}, "demographics": {"Population": 278357000, "LifeExpectancy": 77.0999984741211}} 1 row in set (0.00 sec) And 'easy' is relative, right? Well, what if you had a way to make it easier to read, er, pretty?

MySQL 8.0.1 has a few new JSON functions but one of them is designed to help make your JSON data look pretty. Place JSON_PRETTY around the column desired and out comes better looking JSON data.

{ "GNP": 8510700, "_id": "USA", "Name": "United States", "IndepYear": 1776, "geography": { "Region": "North America", "Continent": "North America", "SurfaceArea": 9363520 }, "government": { "HeadOfState": "George W. Bush", "GovernmentForm": "Federal Republic" }, "demographics": { "Population": 278357000, "LifeExpectancy": 77.0999984741211 } }

Wow! That is an improvement!

But wait! There's more

JSON_PRETTY can also help make non-JSON data pretty JSON. Using JSON_ARRAY() or JSON_OBJECT to create JSON-ized versions of data and then passing those to JSON_PRETTY produces the desired effect.

mysql> SELECT JSON_PRETTY(JSON_ARRAY(ID)), JSON_PRETTY(JSON_OBJECT('Name',Name,'District',District)) FROM city WHERE CountryCode='USA' LIMIT 1; ... | JSON_PRETTY(JSON_ARRAY(ID)) | JSON_PRETTY(JSON_OBJECT('Name',Name,'District',District)) | ... | [ 3793 ] | { "Name": "New York", "District": "New York" }

Cool!

Being a long time programming with languages that used a lot of braces and brackets, I have found it necessary when not using a help IDA that counted matches for brackets/braces for me to hunt for that missing or extra bracket/braces. Looking at JSON data from a simple select can quickly tire you eyes trying to guess how things are grouped in objects. But it is evident between the first and second examples how nicely JSON_PRETTY presents the data.