Re: How to get row to JSON

[Fernando Paiva]

CREATE PROCEDURE row_to_json(IN _schema varchar(255), IN _table varchar(255), IN _filter text)
SQL SECURITY INVOKER
BEGIN
DECLARE handler_code TEXT;
DECLARE handler_msg text;
DECLARE handler_errorNo text;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
GET DIAGNOSTICS CONDITION 1
handler_code = RETURNED_SQLSTATE,
handler_msg = MESSAGE_TEXT,
handler_errorNo = MYSQL_ERRNO;

SELECT JSON_OBJECT('code', handler_code, 'error_number', handler_errorNo, 'message', handler_msg);
END;

DROP TEMPORARY TABLE IF EXISTS temp_row_to_json;
CREATE TEMPORARY TABLE temp_row_to_json (jsonrow TEXT);

SELECT CONCAT('INSERT INTO temp_row_to_json SELECT JSON_OBJECT(', GROUP_CONCAT('''', columnName, ''', ', columnName), ') FROM ', _schema, '.', _table,
(IF(ISNULL(_filter), '', CONCAT(' WHERE ', _filter))))
INTO @query
FROM (
SELECT COLUMN_NAME AS columnName
FROM information_schema.COLUMNS
WHERE (COLUMNS.TABLE_SCHEMA = _schema)
AND (COLUMNS.TABLE_NAME = _table)
) AS columns;

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT COUNT(*)
INTO @rowCount
FROM temp_row_to_json;

IF (@rowCount > 1) THEN
SELECT JSON_OBJECT(CONCAT(_schema, '.', _table), JSON_ARRAYAGG(CAST(jsonrow AS JSON))) AS `json`
FROM temp_row_to_json;
ELSE
SELECT jsonrow AS `json`
FROM temp_row_to_json;
END IF;
END