Блог

В настоящее время мне поручено преобразовать нашу активную ненормализованную таблицу в нормализованную. Мы решили использовать триггеры базы данных, чтобы облегчить массовую миграцию и последующие изменения данных, пока мы не прекратим работу со старой таблицей.

Ниже приведены структура и пример нашей старой таблицы:

 SELECT * FROM TabHmIds;

ID  EntitlementID   TabId   HmId
1   101             201     301
2   102             202     302
  

Требуемая структура и образец нашей новой таблицы должны выглядеть следующим образом:

 SELECT * FROM tab_integration;
id  tab_id  integration_id  metadata
1   201     1               { "paid_id": {"entitlement_id": 101, "id": 301} }
2   202     1               { "paid_id": {"entitlement_id": 202, "id": 302} }
  

Следующее — это то, что я до сих пор делал в своем триггере INSERT:

 CREATE TRIGGER tab_integration_after_insert AFTER INSERT ON `TabHmIds` 
FOR EACH ROW 
BEGIN 

DECLARE var_metadata JSON;
DECLARE var_new_metadata JSON;
DECLARE var_hm_metadata JSON;
DECLARE var_integration_id INT(11);

SELECT
  metadata,
  integration_id INTO var_metadata,
  var_integration_id
FROM
  `go`.`tab_integration` gti
WHERE
  gti.`tab_id` = NEW.`TabId`;

SET var_hm_metadata = JSON_OBJECT('entitlement_id', NEW.`EntitlementId`, 'id', NEW.`HmId`);

IF var_integration_id = 1 THEN 
    
    IF var_metadata IS NULL THEN
        SET var_new_metadata = JSON_OBJECT('paid_id', var_hm_metadata);
        
    ELSE
        SET @paid_id = JSON_UNQUOTE(JSON_EXTRACT(var_metadata, '$.paid_id'));        
        SET var_new_metadata = JSON_ARRAY_APPEND(var_metadata, '$.paid_id', var_hm_metadata);

    END IF;
    
END IF;

UPDATE `tab_integration` gti SET `metadata` = var_new_metadata WHERE `tab_id` = NEW.`TabId`;

END
  

Однако, что я получаю, это:

 SELECT * FROM tab_integration;

id  tab_id  integration_id  metadata
1   201     1               { "paid_id": "{"entitlement_id": 101, "id": 301}" }
2   202     1               { "paid_id": "{"entitlement_id": 202, "id": 302}" }
  

Из приведенной выше таблицы объект JSON преобразуется в СТРОКУ. Я знаю, что JSON_OBJECT преобразует переданное значение в строку. ИТАК, я использовал JSON_UNQUOTE(JSON_EXTRACT(…)) для преобразования значения пути paid_id в JSON, но оно не анализируется в JSON. Я также попробовал JSON_MERGE_PRESERVE, чтобы поместить объект JSON по пути paid_id, но в итоге я получаю:

 {“paid_id”: [], “entitlement_id”: 101, “id”: 301 }
  

Я также попытался поместить массив JSON во временную таблицу с помощью JSON_TABLE и изменить значения во временной и преобразовать эту временную таблицу в JSON с помощью JSONARRAYAGG. Но Workbench продолжает говорить, что у меня ошибка в синтаксисе, хотя я напрямую скопировал примеры из Интернета.

Я также пытался преобразовать правильно сформированную строку в JSON, но Workbench также выдает синтаксическую ошибку. Я потратил неделю на разрешение этой структуры данных в MySQL.

Создание сценариев для баз данных не является моей сильной стороной, и я новичок в функциях JSON в MySQL. Заранее спасибо тем, кто ответит.

Если в случае необходимости, мой MySQL Workbench имеет версию 10.4.13-MariaDB. Но скрипт должен работать в MySQL 5.7.

Я нашел ответ на свою проблему.

Перед вставкой новых данных JSON я проанализировал их в CHAR и заменил символы, превратив их в строку. Я сделал следующее, и это сработало!

 # After performing the needed JSON manipulations, cleanse the JSON string to JSON.

SET var_new_metadata = CAST(var_new_metadata AS CHAR);

SELECT REPLACE(REPLACE(REPLACE(var_new_metadata, '\', ''), '"{', '{'), '}"', '}') INTO var_new_metadata;
  

После очистки данных вызов UPDATE все еще работал и попытался выполнить некоторые манипуляции с JSON после и, да, все еще работает!