how do I Store json objects into mysql table using storedprocedure

Question

I have a JSON like below:

[
{
"Label":"rm_sensor_combined",
"Data":"[{\"Label\":\"name\",\"Value\":\"esd001\",\"Type\":\"String\"},{\"Label\":\"dname\",\"Value\":\"esd\",\"Type\":\"String\"},{\"Label\":\"location\",\"Value\":\"here\",\"Type\":\"String\"},{\"Label\":\"kill\",\"Value\":\"yes\",\"Type\":\"String\"}]",
"Where":"[{\"Label\":\"ExternLineNo\",\"Value\":\"10\",\"Type\":\"String\"},{\"Label\":\"ExternReceiptKey\",\"Value\":\"20\",\"Type\":\"String\"},{\"Label\":\"SKU\",\"Value\":\"got it\",\"Type\":\"String\"}]",
"Option":"INSERT"
}
]

I would like to store them into a table using a store procedure, the table should look like the below:

Label	Data	Where	Option
rm_sensor_combined	data Json	Where Json	insert

any advice would be appreciated. Thanks

below is my stored procedure like:

CREATE PROCEDURE `sp_getSaveJsonData`(ajs_param json)
BEGIN

    drop table if exists ToSaveTable;
    CREATE TEMPORARY TABLE ToSaveTable (
      `Label` LONGTEXT,
      `Data` LONGTEXT,
      `Where` LONGTEXT,
      `Option` nvarchar(1000)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    SELECT JSON_EXTRACT(ajs_param, '$[*]') INTO @LblArray;
    SELECT JSON_LENGTH(@LblArray) INTO @jsonLength;
    
    set @counter = 0;
    while @counter < @jsonLength do 
        select json_extract(@LblArray, concat('$[',@counter,']'))  into @rowData;
        insert into fromJsonTable(`Label`, `Data`, `Where`, `Option`) 
        values( REPLACE(json_extract(@rowData, '$.Label'), '"', ''), 
                REPLACE(json_extract(@rowData, '$.Data'), '"', ''), 
                REPLACE(json_extract(@rowData, '$.Where'), '"', ''), 
                REPLACE(json_extract(@rowData, '$.Option'), '"', '')
            );
        set @counter = @counter + 1;
    end while;
    
END

What's your stored procedure looks like? check https://stackoverflow.com/questions/61933939/mysql-save-json-data-via-stored-procedure-in-node-js — Kamran Shahid, May 04 '22 at 09:08
@Akina, i can't use the common INSERT .. SELECT because i need a reusable function — bfernKim, May 04 '22 at 09:19
No problems to put single INSERT .. SELECT into SP. And shown JSON is not valid, at least from MySQL looking point. `"` in the value must be quoted. — Akina, May 04 '22 at 09:22
Your example of JSON is not valid JSON, so none of the JSON functions will work on it. Try testing the example JSON with `JSON_VALID()`. It returns 0 (i.e. not valid JSON syntax). The reason is that some of the values in the JSON document contain unescaped double-quote characters. — Bill Karwin, May 04 '22 at 13:23
@BillKarwin, by using mssql, i could easily handle this task with the same Json, the thing is that i am a new in mysql and the project i have is using mysql. i don't think the json has a problem. thanks for the reaction. — bfernKim, May 05 '22 at 05:21

how do I Store json objects into mysql table using storedprocedure

0 Answers0