Convert MySQL query result to JSON

The project I'm working on requires to save all of the DB operations. So when there will be added new user I've to log the date, operation type like 'INSERT', 'UPDATE', 'DELETE' and all user data. The project is in the development phase so the columns in User table are changing.

This what I plan to do is to select the new user data from the Users table and insert them to UserLog table as a JSON column.

Is it possible to convert SELECT * FROM table_name to JSON format? I know that there is a possibility to convert separated columns by JSON_OBJECT function, but as I mentioned above, the columns are floating so I would be forced to change the JSON_OBJECT names each time I change anything in the main table. And there are a lot of tables!

It should work like this:

CREATE TABLE Users (
    id INT(1) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(30) NOT NULL,
    lastName VARCHAR(30) NOT NULL,
    email VARCHAR(50),
)

The query:

SELECT * FROM Users;

Should return:

[
    {
        "id": 1,
        "firstName": "Lucas",
        "lastName": "Smith",
        "email": "[email protected]"
    },
    {
        "id": 2,
        "firstName": "Ben",
        "lastName": "Kovalsky",
        "email": "[email protected]"
    },
    ...
]

Is there a simple solution to solve this problem? If not, what is your strategy for logging DB operations?

I'm not up to date with MySQL as I switched over to PostgreSQL but I found that the recent MySQL, from version 8, supports JSON:

SELECT JSON_ARRAYAGG(
  JSON_OBJECT(
    'id', `id`,
    'firstName', `firstName`,
    'lastName', `lastName`,
    'email', `email`
  )
)
FROM Users;

should work.

Edit, sources:

https://dev.mysql.com/doc/refman/8.0/en/json.html#json-values
https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_json-arrayagg

Convert MySQL query result to JSON

Related

Recent Posts