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": "lucas@def.com"
    },
    {
        "id": 2,
        "firstName": "Ben",
        "lastName": "Kovalsky",
        "email": "ben@def.com"
    },
    ...
]

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

score 3 · Answer 1 · answered Apr 08 '20 at 09:30

3

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:

answered Apr 08 '20 at 09:30

adiDX

49
1
8

1

I have 50+ columns, that's why I mentioned in description that `JSON_OBJECT` way is incorrect... – LukaszTaraszka Apr 08 '20 at 10:01
As mentioned above, it is preferable to do conversion at client side. What connector you're using to get the data from the MySQL? We might find something there – adiDX Apr 08 '20 at 14:04
I agree with you. I've already done it on client site but I hope it could be possible on DB side. I'm using ADO.NET with C# :( (not my choice). – LukaszTaraszka Apr 08 '20 at 14:26

Mark Longhurst · Answer 2 · 2022-01-18T05:35:38.317

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.

Elzo Valugi · Answer 3 · 2020-04-08T15:17:31.923

Normally converting the output to JSON or any other format is a job for the programming language or your mySQL IDE, but there is a way also from mySQL.

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-json-output.html

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-json-wrapping.html

Directly from documentation:

MySQL  localhost:33060+ ssl  world_x  JS > shell.options.set('resultFormat','json')
MySQL  localhost:33060+ ssl  world_x  JS > session.sql("select * from city where countrycode='AUT'")
{
    "ID": 1523,
    "Name": "Wien",
    "CountryCode": "AUT",
    "District": "Wien",
    "Info": {
        "Population": 1608144
    }
}
{
    "ID": 1524,
    "Name": "Graz",
    "CountryCode": "AUT",
    "District": "Steiermark",
    "Info": {
        "Population": 240967
    }
}
{
    "ID": 1525,
    "Name": "Linz",
    "CountryCode": "AUT",
    "District": "North Austria",
    "Info": {
        "Population": 188022
    }
}
{
    "ID": 1526,
    "Name": "Salzburg",
    "CountryCode": "AUT",
    "District": "Salzburg",
    "Info": {
        "Population": 144247
    }
}
{
    "ID": 1527,
    "Name": "Innsbruck",
    "CountryCode": "AUT",
    "District": "Tiroli",
    "Info": {
        "Population": 111752
    }
}
{
    "ID": 1528,
    "Name": "Klagenfurt",
    "CountryCode": "AUT",
    "District": "Kärnten",
    "Info": {
        "Population": 91141
    }
}
6 rows in set (0.0031 sec)

Also, adding the JSON_OBJECT, that is available from 5.7+, see the answer here.

mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
+-----------------------------------------+
| JSON_OBJECT('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------

It's a shell presenting option but I planned to get it as a query result, like in JSON_OBJECT. Is there a way to return a select all as a JSON string and insert it to a row in another table? — LukaszTaraszka, Apr 08 '20 at 11:31
@LukaszTaraszka There is also this: https://stackoverflow.com/questions/41758870/how-to-convert-result-table-to-json-array-in-mysql#41759147 — Elzo Valugi, Apr 08 '20 at 15:14
Thanks, Elzo but this is the same as @adiDX wrote below. I've 50+ columns which are changing in time so I couldn't use JSON_XXX solutions - the column names must be specified in that. I thought it's possible to do that natively in MySQL. Finally, I've made it in my DB provider in source code. — LukaszTaraszka, Apr 10 '20 at 08:57
The linked documentation is false. the mysql command does not have a "--result-format" option" `$ mysql -e "select * from gdprt_trunk.uid_delete_deleteduidbatch;" --result-format=json mysql: [ERROR] unknown variable 'result-format=json'. ` — Matthew Scouten, May 24 '22 at 21:22

Convert MySQL query result to JSON

3 Answers3