0

Deal all, I have a JSON data as follows:

set @injsondata = '{
    "action": "SaveEventByMinutes",
    "eventbyminutes": [{"matchid":10001,"minute":"1","halftime":"1","value":"A","userid":1,"createddate":"2021-09-25"},
                       {"matchid":10001,"minute":"2","halftime":"1","value":"X","userid":1,"createddate":"2021-09-25"}]}';

Now I want to change the matchid value to 10002 using select statement in MySQL.

What I have tried 

 1. select JSON_merge_patch(@injsondata,'{"eventbyminutes[*]":{"matchid": 10002}}');
 2. SELECT JSON_replace(@injsondata, '{"eventbyminutes":{"matchid":2}}');

But nothing is working
Is there any other option to do this?
or do I have to create a function or procedure like 

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_modify_json`(injsondata json) RETURNS json
    DETERMINISTIC
begin
    declare len int default json_length(injsondata);
    declare i int default 0;
    declare outjsondata json;

    while i < len do
        # Replace the report4 property of the i'th element with an empty list
        set outjsondata = JSON_replace(injsondata,'$[0].eventbyminutes[i].matchid',10002);
        set i = i + 1;
    end while;

    return outjsondata;
end$$
DELIMITER ;

Something like above?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Tanmoy Banerjee
  • 45
  • 8
  • 1
    You would find this much easier if you set up a proper table structure to handle things instead of trying to shoehorn stuff into a single column in JSON format. – Tangentially Perpendicular Oct 03 '21 at 22:31
  • Dear Bill, the scenario is something like an excel file will be uploaded from front end then angular will parse that excel file and prepare a json and the .net middle tier will call a MySQL procedure and that json will be passed from it. I have already created a table with this structure and there these data is inserted using json object like insert into table and values are mapped through columns – Tanmoy Banerjee Oct 04 '21 at 11:58

0 Answers0