Manipulating the Json data structure stored in mysql text column

You could start first of all by manipulating given JSON structure. Assuming that the structure will remain the same i.e. [ {}, {}, {}, {}, ...]. Lets start with getting the content from your 1st object

SET @a := '[{"abc":"1","def":2,"xyz":3}]';
SET @b := JSON_REMOVE( @a, '$[0].def', '$[0].xyz' );
SET @c := JSON_REMOVE( @a, '$[0].abc' );
SET @d := JSON_EXTRACT( @c, '$[0]');


SET @e := JSON_INSERT( @b, '$[0].change', JSON_QUERY( @d, '$' ) ); -- for maria db
/** SET @e := JSON_INSERT( @b, '$[0].change', CAST( @d AS JSON ) ); -- for mysql **/

There has to be a more efficient way to do the aforementioned

I've done all this in mariadb, so you will need to uncomment and possible do some corrections for mysql

We now need to identify the parts that we use each and every time to modify.

• the array index
• the key to keep
• the keys to move

Assuming that the table containing the data is something like that

DROP TABLE IF EXISTS `json_test`;

/**
-- mysql
CREATE TABLE `json_test` (
  `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `json` JSON NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
*/

-- maria db
CREATE TABLE `json_test` (
  `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
  `json` LONGTEXT NOT NULL,
  PRIMARY KEY(`id`),
  CHECK( JSON_VALID(`json`) )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- add some values to test
INSERT INTO `json_test`(`json`) VALUES
    ( '[{"abc":"1","def":2,"xyz":3}]' ),
    ( '[{"abc":"1","def":2,"xyz":3},{"abc":"1","def":2,"xyz":3}]' );

So to manipulate the content and lets say update it via a procedure

DELIMITER //

/**
 * This proc uses prepared statements to be able to reproduce the aforementioned example
 *
 * @note:   I am not capturing cases of warnings/errors those you will have to add them by yourself.
 * 
 * @param: `INindex` - INT - the position in the json array
 * @param: `INkeep` - VARCHAR(32) - the value to keep, format /\.[a-z]/
 * @param: `INmove` - VARCHAR(128) - the values to move, format /^(\.[a-z], )*\.[a-z]$/
 * @param: x any other parameter that you would like to add
 */
DROP PROCEDURE IF EXISTS `proc_update_json_test` //
CREATE PROCEDURE `proc_update_json_test`( IN `INid` SMALLINT(4), IN `INindex` INT, IN `INkeep` VARCHAR(32), IN `INmove` VARCHAR(128) )
`bgn_lbl`:BEGIN -- or just begin, use labels to escape some conditions
  DECLARE `arrayPos` VARCHAR(32) DEFAULT CONCAT( '$[',`INindex`,']');
  DECLARE `encodedJSON` LONGTEXT; -- for mariadb
  /** DECLARE `encodedJSON` JSON; -- for mysql **/

  IF ( SELECT COUNT(*) FROM `json_test` ) = 0 THEN
    SELECT 'no such id. Exiting';
    LEAVE `bgn_lbl`;
  END IF;

  SELECT `json` INTO `encodedJSON` FROM `json_test` WHERE `id` = `INid`;

  SET `INkeep` = REPLACE( `INkeep`, '.', CONCAT( `arrayPos`, '.' ) );
  SET `INmove` = REPLACE( `INmove`, '.', CONCAT( `arrayPos`, '.' ) );
  SET `INmove` = REPLACE( `INmove`, ',', "', '" );

  PREPARE `stmt` FROM CONCAT( "SET @b := JSON_REMOVE( '",`encodedJSON`,"', '",`INmove`,"' )" );
  EXECUTE `stmt`;
  DEALLOCATE PREPARE `stmt`;

  -- since this is not feasible to add more than a single key unless if we declare it as a
  SET @c := JSON_REMOVE( `encodedJSON`, `INkeep` );
  SET @d := JSON_EXTRACT( @c, `arrayPos` );

  UPDATE `json_test`
    SET `json` = JSON_INSERT( @b, CONCAT( `arrayPos`, '.change' ), JSON_QUERY( @d, '$' ) ) -- for maria db
    /** `json` = JSON_INSERT( @b, CONCAT( `arrayPos`, '.change' ), CAST( @d AS JSON ) ) -- for mysql **/
    WHERE `id` = `INid`;
END //

DELIMITER ;

To run the procedure and check your results

CALL `proc_update_json_test`( 2, 1, '.abc', '.def, .xyz' );
SELECT * FROM `json_test`;

You will need to experiment a lot to make this work in any structure you wish. It is just to get the idea how to access data

Resources MySQL JSON, JSON with MariaDB, JSON Functions MariadDB

Further reading Create Procedure MySQL and Prepared Statements

My opinion is that you update that column with the desired values of yours.. Because let's say you use stored procedure and retrieve that column and do some string manipulation task, it only delays your system's performance. Considering that and the simplicity I recommend you just refer that column with related ID and update to your desired value.