Generate a json object using column names as keys-CodePudding

Is it possible to generate a json object using the column name as keys automatically? I have a table with many columns and I need to dump it into a json object. I know I can do this using the JSON_OBJECT function but I was looking for a more condensed syntax that would allow me to do this without having to specify the name of all the columns

SELECT JSON_OBJECT("col_a", m.col_a, "col_b", m.col_b, "col_c", m.col_c, ...)
  FROM largetable AS m

Something like this?

SELECT JSON_OBJECT(m.*)
  FROM largetable AS m

I'm using MariaDB version 10.8.2

CodePudding user response：

Json objects make sense in other languages like javascript, C#... There are many libraries to convert the result of a MariaDB query into a list of json objects in most languages. Also, a good practice is to make the database engine do as little effort as possible when performing queries and processing the result in the application.

CodePudding user response：

This is of course not possible, since the parser would not accept an odd number of parameters for the JSON_OBJECT function.

To do that in pure SQL, you can't do that within a single statement, since you need to retrieve the column names from information_schema first:

select @statement:=concat("SELECT JSON_OBJECT(", group_concat(concat("\"",column_name,"\"", ",", column_name)),") FROM mytable") from information_schema.columns where table_name="mytable" and table_schema="test";

prepare my_statement from statement;

execute my;

Much easier and faster is to convert the result in your application, for example in Python:

import mariadb, json

conn= mariadb.connect(db="test")
cursor= conn.cursor(dictionary=True)
cursor.execute("select * from mytable")
json_row= json.dumps(cursor.fetchone())