Export MySQL query results as JSON

In one of the latest post, I discussed how to transform a large MySQL table to JSON using Apache Spark. Well, that approach works for any tables with any volume. But it’s an overkill for simple and small tables. The good news is MySQL by default has a feature for that. In this article, I cover how to export MySQL query results as JSON.

We will use two aggregate functions of MySQL that are JSON_ARRAYAGG and JSON_OBJECTAGG. They are introduced in MYSQL 5.7.22. So the solution works on any MySQL >= 5.7.22.

To have an example to work with, let say we have a users table containing users data with the following data structure,

Now, we insert a couple of example to the table,

Now we want to transform the content of the table to the following JSON structure,

From the above data structure, there are two things to consider. One is rows transformations to JSON. And the next is wrapping them to JSON array. For that, we write the query as follows,

But the result is printed on the screen. What if we want them in a JSON file?

That’s very easy. All we have to is to put the query in a file, query.sql and run the following command,

Keep in mind that in query.sql we need to specify the database name in the first line. For example,