Convert MySQL query result to JSON

Question

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?

Answer 1

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

Answer 2

I know this is an old thread, but for anyone still facing this issue, there is a way to convert the result set into json without knowing the column names. The key is to get the names of the columns in a string like 'column_1', column_1, 'column_2', column_2, ... and then use this string in a prepared query.

SET @column_name_string_for_query = "";
  
SHOW COLUMNS
FROM your_table_name
WHERE @column_name_string_for_query := TRIM(", " FROM CONCAT("'", Field, "', ", Field, ", ", @column_name_string_for_query));

SET @query_string = concat("
  SELECT JSON_ARRAYAGG(JSON_OBJECT(", @column_name_string_for_query, ")) 
  FROM your_table_name"
);
PREPARE statement FROM @query_string;
EXECUTE statement;
DEALLOCATE PREPARE statement;

You could also get the column names from INFORMATION_SCHEMA.COLUMNS, but that only works for tables that are not temporary tables. The solution above works for both temporary tables and normal tables.

You could also save this as a stored procedure for ease of use.