Generating Newline-Delimited JSON (NDJSON) Using JSON_OBJECT() In MySQL 5.7.32
After posting yesterday's exploration of
position: sticky on the horizontal axis, Patrick Finegan - one of our Customer Success Managers at InVision - felt that it would make for a great conversation with one of our customers. As such, I had to generate a stand-alone version of the demo using "real data". And, since my Angular app was expecting said data in a particular format, I ended up using the
JSON_OBJECT() function, introduced in MySQL 5.7, to generate Newline-Delimited JSON (NDJSON) data that I could plug right into my Angular app.
DATETIME values as UTC Milliseconds. And, I often (though not always) return my
TINYINT values as
ASIDE: I like to return Date/Time values as UTC Milliseconds so that I can generate
Dateobjects in the client using
new Date( milliseconds ). This creates a
Dateobject in the client's Time Zone. It's also nice to have the raw values in UTC Milliseconds because it becomes easily comparable to
To get my demo data into the right format, I used
JSON_OBJECT() to explicitly construct a JSON payload out of each row. This gave me the opportunity to get the return values into the right format. Given a
friend table with the following structure:
CREATE TABLE `friend` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `isBFF` tinyint(1) unsigned NOT NULL, `createdAt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
... I was able to generate my NDJSON results using something akin to the following SQL statement:
JSON_OBJECT() function is a variadic function; meaning it accepts a variable number of arguments. In this case, the arguments consist of a set of object keys interlaced with a set of object values. In other words, it takes a series of key-value pairs and returns the generated
JSON type. MySQL then serializes said
JSON type into a JSON String as it generates the result-set. And, when we run this SQL query in MySQL 5.7.32, we get the following output:
createdAt values have been massaged into
Boolean and UTC Millisecond values, respectively. So much winning!
ASIDE: At this point, in order to drop this data into my Angular app, all I had to do was add a comma to the end of each line and jam it into an Array. This was a trivial effort using the extraordinary power of multiple cursors in SublimeText 3 (ST3).
JSON is the de facto standard for data interoperability. And, with the slew of JSON-related functionality introduced in MySQL 5.7, I now have a lot of options when it comes to generating data. In this case, I was able to use the
JSON_OBJECT() function to return a set of records as Newline-Delimited JSON (NDJSON) without breaking a sweat!
Want to use code from this post? Check out the license.
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
❤️ ❤️ Thank You For Your Comment ❤️ ❤️
In order to keep this website fun, safe, and full of those sweet, sweet feelings, your comment has been submitted for approval.