Parse JSON And Insert Into MySQL

Question

I am receiving JSON data to my server from each client. I have three main tables; datatypes, templaricustomers and mqttpacket.

Here the datatypes are coming from JSON variable names and I am keeping them in the database.

As I am a beginner in MySQL, I am trying to make a loop and insert the parsed JSON to related tables.

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_INSERT_DATA`(
    IN `incoming_data` TEXT,
    IN `value_array` TEXT,
    IN `customer_id` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE i INT;
DECLARE value_iteration VARCHAR(50);
DECLARE lcl_data_type_id INT;

    SET i = 1;

    WHILE (LOCATE(',', value_array) > 0)
    DO
        SET @arr_data_type_name = SUBSTRING_INDEX(value_array,',',i);
        SET value_array = SUBSTRING(value_array, LOCATE(',',value_array) + 1);

        SELECT JSON_EXTRACT(@incoming_data, @arr_data_type_name) INTO value_iteration;
        
        SET @arr_data_type_name := SUBSTRING_INDEX(@arr_data_type_name, ".", -1);
        
        SELECT id INTO lcl_data_type_id FROM test_database.datatypes WHERE datatypes.data_name = @arr_data_type_name LIMIT 1;
        
        INSERT INTO test_database.mqttpacket (data_type_id,inserted_time,customer_id,data_value) VALUES(lcl_data_type_id,NOW(),customer_id,value_iteration);
        SET i = i+1;
    END WHILE;

END

Example incoming_data in JSON is like;

{"d": {"subcooling": 6,"B1": 382,"B2": 386,"B3": 526,"B4": 361,"B5": 713,"B6": 689,"B7": 386,"B8": 99,"Discharge": 663,"Suction": 111,"High_Pressure": 225,"Low_Pressure": 78,"Evaporation": 31,"Condensation": 388,"MAX_CMP_SPEED": 950,"Thermal_Limit": 950,"SH": 78,"EEV_pct": 571,"COP": 52,"DSH": 272,"Water Flux": 713,"Fan Power": 239,"Delta T to Start": 0,"Delta P to Start": 60,"CMP_ROTOR_RPS": 430,"SET_CH_FLASH": 120,"SET_HP_FLASH": 500,"SET_DHW_FLASH": 500,"Defrosting": 0,"B8_AVERAGE": 42,"SET_PLANT": 0,"SET_CH_BMS": 430,"SET_HP_BMS": 382,"SET_DHW_BMS": 510,"SET_ACTIVE": 402,"SET_DSH": 323,"EEV_INJ_pct": 0,"LPT": 0,"HPT": 0,"PLANT_MODE_MANUAL": 0,"DHW_MODE_MANUAL": 0,"WATER_FLOW": 713,"DISCHARGE_TMP": 663,"INVERTER_TMP": 25,"ENVELOP_ZONE": 1,"EEV_A_STEPS": 274,"EBM_POWER": 239,"EBM_MAX_POWER": 322,"COMP_pct_FINAL": 359,"TOTAL_POWER_ABSORBED": 2599,"NAME": [17236,11585,13388,50,0,0,0,0,0,0,0,0,0,0,0,0],"POWER_OUT_KW": 134,"COOLING CAPACITY": [35],"EBM1_PCT": [861],"EBM2_PCT": [767]},"ts": "2021-02-02T14:42:02.479731" }

An example of value_array is like;

$.d.subcooling,$.d.B1,$.d.B2

This is my Stored Procedure. I just need to extract the JSON node by node and find the "datatypename" which is "node name" from "incoming_data" and insert into mqtt_packet table by it's value..

It's not able to fetch the data which is "value_iteration" and inserts unrelated data type ids.. Please advise me what is wrong with my query. I hope I was clear... Cheers!

question from:https://stackoverflow.com/questions/66047836/parse-json-and-insert-into-mysql

Answer 1

Waitting for answers