How to get row to JSON

[Kevin Waterson]

I am trying to get a stored procedure to return the json representation of row in a table.
This test works:


DROP procedure IF EXISTS tbl_list;
DELIMITER //
Create procedure tbl_list(table_name Varchar(40), id INT)
BEGIN
SET @A:= CONCAT('Select * from',' ',table_name,' WHERE id=',id);
Prepare stmt FROM @A;
EXECUTE stmt;
END //
CALL tbl_list('profession', 3);


However, when I try to convert this to return JSON, the procedure compiles fine, but when run, I get the following error.
SQL Error(1241): Operand should contain 1 columns(s).
I understand the error is about the number of columns, and seems to do with the first SET statement.. Not sure when setting something causing an error, or if it is the @fields variable...


DROP procedure IF EXISTS row_to_json;
DELIMITER //
Create procedure row_to_json(table_name Varchar(50), id INT)
BEGIN
SET @fields = (
'select group_concat(\'`\', column_name, \'`\')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=',' ',table_name
);
SET @stmt:= CONCAT('SELECT JSON_OBJECT(', @fields ,') from ',table_name,' WHERE id=',id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
CALL row_to_json('profession', 3);