SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE IF NOT EXISTS my_table ( ID INT, UF_EN VARCHAR(255), UF_RU VARCHAR(255), UF_NAME VARCHAR(255), UF_GROUP VARCHAR(255), UF_SORT VARCHAR(255), UF_BOLD VARCHAR(255), UF_GENERAL VARCHAR(255) ); INSERT INTO `my_table` (`ID`, `UF_EN`, `UF_RU`, `UF_NAME`, `UF_GROUP`, `UF_SORT`, `UF_BOLD`, `UF_GENERAL`) VALUES (12, 'Other Than Fixed Noncurrent Assets', 'Кроме основных внеоборотных активов', 'OtherThanFixedNoncurrentAssets', 'balance_sheet', 2, 0, 0); INSERT INTO `my_table` (`ID`, `UF_EN`, `UF_RU`, `UF_NAME`, `UF_GROUP`, `UF_SORT`, `UF_BOLD`, `UF_GENERAL`) VALUES (13, 'Participating Securities, Distributed And Undistributed Earnings/Loss, Basic', 'Дивиденды по привилегированным акциям и др. корректировки', 'ParticipatingSecuritiesDistributedAndUndistributedEarningsLossBasic', 'income_statement', 13, 0, 0); INSERT INTO `my_table` (`ID`, `UF_EN`, `UF_RU`, `UF_NAME`, `UF_GROUP`, `UF_SORT`, `UF_BOLD`, `UF_GENERAL`) VALUES (14, 'Redeemable Noncontrolling Interest', 'Подлежащая погашению неконтролирующая доля', 'RedeemableNoncontrollingInterest', 'balance_sheet', 12, 0, 0); INSERT INTO `my_table` (`ID`, `UF_EN`, `UF_RU`, `UF_NAME`, `UF_GROUP`, `UF_SORT`, `UF_BOLD`, `UF_GENERAL`) VALUES (15, 'Equity Attributable To Noncontrolling Interest', 'Собственный капитал, относящийся к неконтролирующей доле', 'EquityAttributableToNoncontrollingInterest', 'balance_sheet', 7, 0, 0); INSERT INTO `my_table` (`ID`, `UF_EN`, `UF_RU`, `UF_NAME`, `UF_GROUP`, `UF_SORT`, `UF_BOLD`, `UF_GENERAL`) VALUES (16, 'Assets', 'Активы', 'Assets', 'balance_sheet', 5, 1, 1); INSERT INTO `my_table` (`ID`, `UF_EN`, `UF_RU`, `UF_NAME`, `UF_GROUP`, `UF_SORT`, `UF_BOLD`, `UF_GENERAL`) VALUES (17, 'Noncurrent Assets', 'Внеоборотные активы', 'NoncurrentAssets', 'balance_sheet', 3, 1, 0); DROP FUNCTION IF EXISTS dynam_parse; DELIMITER // CREATE FUNCTION dynam_parse(dynam JSON) RETURNS JSON COMMENT 'Парсит поле dynam и отдаёт JSON с параметрами: [param_code:{title, value}]' LANGUAGE SQL NOT DETERMINISTIC BEGIN DECLARE i INT DEFAULT 0; DECLARE param VARCHAR(255); DECLARE param_data JSON DEFAULT '{"code":null,"name":null,"sort":null,"value":null}'; DECLARE params JSON DEFAULT '{}'; DECLARE dynam_keys JSON default JSON_KEYS(dynam); SET param_data = '{}'; WHILE i < JSON_LENGTH(dynam_keys) DO SET param = JSON_EXTRACT(dynam_keys, CONCAT('$[', i , ']')); SET param_data = (SELECT JSON_OBJECT('value', JSON_EXTRACT(dynam, CONCAT('$.', param)),'code', UF_NAME, 'name', UF_RU, 'sort', UF_SORT, 'is_bold', UF_BOLD) FROM my_table WHERE UF_EN = param); SET params = JSON_INSERT(params, CONCAT('$.', param ), param_data); SET i = i + 1; END WHILE; RETURN params; END // DELIMITER ; SELECT dynam_parse('{"Assets": "123", "test1": "456"}') as result;