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;
Telegram: DB developers channel