Updating the Fields in the JSON_ARRAY Mysql 8.0

Here is my scenario, I want to update the hourly_rate for the BOB to 600. How to extract the hourly_rate from the json_array mentioned below for the specific user BOB.

@data = [{
 "Subject": "Maths",
 "type": "paid",
 "tutor": "MARY",
 "hourly_rate": "500"
},
{
 "Subject": "Maths",
 "type": "paid",
 "tutor": "BOB",
 "hourly_rate": "700"
}]

Can I use JSON_SEARCH() to get the index by using Where Clause.

example:

  "Select JSON_SET(@data,'$[*].hourly_rate', 600) Where 'Subject' = Maths and 'tutor' = 'BOB'";

Answer

I got this working. But I had to use a view in order to get cleaner code.

My answer is based on this one: https://stackoverflow.com/a/51563616/1688441

---

Update Query

Fiddle @ https://www.db-fiddle.com/f/7MnPYEJW2uiGYaPhSSjtKa/1

UPDATE test
  INNER JOIN getJsonArray ON getJsonArray.tutor = 'BOB'
SET test =
  JSON_REPLACE(
    test,
    CONCAT('$[', getJsonArray.rowid - 1, '].hourly_rate'), 600);

select * from test;    

---

Ddl

CREATE TABLE `test` (
  `test` json DEFAULT NULL
);

INSERT INTO `test` (`test`)
VALUES ('[{
 "Subject": "Maths",
 "type": "paid",
 "tutor": "MARY",
 "hourly_rate": "500"
},
{
 "Subject": "Maths",
 "type": "paid",
 "tutor": "BOB",
 "hourly_rate": "700"
}]');

create view getJsonArray as    
select data.* 
from   test, json_table(
  test,
         "$[*]"
         COLUMNS(
          rowid FOR ORDINALITY,
            Subject VARCHAR(100) PATH "$.Subject" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
            type VARCHAR(100) PATH "$.type" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
            tutor VARCHAR(100) PATH "$.tutor" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
            hourly_rate JSON PATH "$.hourly_rate" DEFAULT '{"x": 333}' ON EMPTY
         )
       ) data
      ;