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,

CREATE TABLE IF NOT EXISTS users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(512) NOT NULL,
  last_name VARCHAR(512) NOT NULL,
  age INTEGER NOT NULL
) engine=InnoDB;

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

INSERT IGNORE INTO users VALUES(1, "John", "Wick", 45);
INSERT IGNORE INTO users VALUES(1, "Jeo", "Doe", 34);
INSERT IGNORE INTO users VALUES(1, "Mike", "Miller", 20);
INSERT IGNORE INTO users VALUES(1, "Marshall", "Mathers", 48);

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

[
  {
    "id": 1,
    "age": 45,
    "lastName": "Wick",
    "firstName": "John"
  },
  {
    "id": 2,
    "age": 34,
    "lastName": "Doe",
    "firstName": "Joe"
  },
  {
    "id": 3,
    "age": 20,
    "lastName": "Miller",
    "firstName": "Mike"
  },
  {
    "id": 4,
    "age": 48,
    "lastName": "Mathers",
    "firstName": "Marshall"
  }
]

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,

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'firstName', first_name, 'lastName', last_name, 'age', age)) FROM users;

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,

$ mysql -u root -p < query.sql | tail -1 > out.json

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

USE user;
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'firstName', first_name, 'lastName', last_name, 'age', age)) FROM users;

Inline/featured images credits

• Drawing guy by Campaign Creators on Unsplash