MySQL Forums
Forum List  »  Stored Procedures

Re: How to get row to JSON
Posted by: Fernando Paiva
Date: June 11, 2020 02:34PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
910
January 16, 2020 09:48PM
256
January 17, 2020 12:47AM
Re: How to get row to JSON
158
June 11, 2020 02:34PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.