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.