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.

I am sure that every database GUI (Graphical User Interface) provides some sort of export mechanism whereby you can export a given recordset as CSV (Comma Separated Values), XML, or perhaps even JSON (JavaScript Object Notation). But, in my case, I needed to "massage" the data as part of that export. For example, when inter-operating with JavaScript, I always return my DATETIME values as UTC Milliseconds. And, I often (though not always) return my TINYINT values as true/false Booleans.

ASIDE: I like to return Date/Time values as UTC Milliseconds so that I can generate Date objects in the client using new Date( milliseconds ). This creates a Date object in the client's Time Zone. It's also nice to have the raw values in UTC Milliseconds because it becomes easily comparable to Date.now().

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:

SELECT
	JSON_OBJECT(
		'id', f.id,
		'name', f.name,
		-- isBFF is stored as a TINYINT. In order to cast it to a proper BOOLEAN value,
		-- we need to compare it with the in-built BOOLEAN type. This will implicitly
		-- cast to TRUE or FALSE.
		'isBFF', ( f.isBFF IS TRUE ),
		-- createdAt is stored as a DATETIME. In order to make it a bit more consumable
		-- in JavaScript, I like to convert it to UTC Milliseconds.
		'createdAt', ( UNIX_TIMESTAMP( f.createdAt ) * 1000 )
	) AS payload
FROM
	friend f
ORDER BY
	f.id ASC
;

The 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:

As you can see, each record is returned as a JSON (JavaScript Object Notation) payload. And, my isBFF and 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.

Short link: https://bennadel.com/3962