Unfortunately, MySQL doesn't offer a function like
SELECT JSON_OBJECT(*) FROM table
Now I'm wondering if anybody has already written a function that perform this task. What I want to achieve is to make aSELECT * statement from any given table that returns a JSON_OBJECT.
Of corse it would be possible to create this manually for each given table. But I'm looking for a more generic approach, where I don't need to specify all columns manually.
You can't do this without dynamic SQL. JSON_OBJECT() takes arguments that must be fixed key/value pairs. If you want a JSON_OBJECT() expression that produces a key for each column of a table, then consult the INFORMATION_SCHEMA and generate a list of expressions, then use that list in a dynamic SQL query.
Example:
mysql> create table mytable (id serial primary key, name text, created_at datetime);
mysql> insert into mytable set name='Bill', created_at=NOW();
mysql> select * from mytable;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | Bill | 2021-09-19 17:37:05 |
+----+------+---------------------+
mysql> select concat('select json_object(',
group_concat(concat(quote(column_name), ', ', column_name)),
') from mytable;') into @sql
from information_schema.columns
where table_schema = 'test' and table_name = 'mytable';
mysql> select @sql;
+----------------------------------------------------------------------------------+
| @sql |
+----------------------------------------------------------------------------------+
| select json_object('id', id,'name', name,'created_at', created_at) from mytable; |
+----------------------------------------------------------------------------------+
mysql> prepare stmt from @sql;
mysql> execute stmt;
+-----------------------------------------------------------------------+
| json_object("id", id,"name", name,"created_at", created_at) |
+-----------------------------------------------------------------------+
| {"id": 1, "name": "Bill", "created_at": "2021-09-19 17:37:05.000000"} |
+-----------------------------------------------------------------------+