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,
Inline/featured images credits
- Drawing guy by Campaign Creators on Unsplash