drop procedure if exists row_to_json;
delimiter go
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;
go
delimiter ;

Last updated 11 Jun 2024