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'";
Advertisement
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 ;