CODEWITHSUNDEEP
jsonsql-updatemariadbmariadb-10.4
ErickBest
ErickBest

Reputation: 4692

Mysql JSON update key value

I have a table like:

CREATE TABLE `campus_tb` (
 `campus_id` int(11) NOT NULL AUTO_INCREMENT,
 `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`campus_dataJSON`)),
 PRIMARY KEY (`campus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

INSERT INTO `campus_tb`( `campus_dataJSON`) VALUES ( '[
                          {"id":"100","name":"James","age":"17","course":"IT"},
                          {"id":"101","name":"Eric","age":"19","course":"CS"},
                          {"id":"102","name":"Julie","age":"21"}]')




+--------------------+-----------------------------------------------------------+
| campus_id          |  campus_dataJSON                                          | 
+--------------------+-----------------------------------------------------------+
| 1                  |  [
                     |     {"id":"100","name":"James","age":"17","course":"IT"},
                     |     {"id":"101","name":"Eric","age":"19","course":"CS"},
                     |     {"id":"102","name":"Julie","age":"21"}
                     |
                     |  ] 
----------------------------------------------------------------------------------  
| 2                  |  [
                     |     {"id":"34","name":"Mimi","age":"18","course":"IT"},
                     |     {"id":"35","name":"Jose","age":"20","course":"CS"},
                     |     {"id":"36","name":"Ken","age":"24"}
                     |
                     |  ]
----------------------------------------------------------------------------------

Am using MariaDB 10.4

  1. MySql query UPDATE the details for Julie of campus_id = 1 to add a "course" for her to become like:

    {"id":"102","name":"Julie","age":"21","course":"MASCOM"},

  2. Mysql Query to UPDATE from "Eric" to "Erick" with a "k" at the end

I have tried so far:

UPDATE `campus_tb` 
SET `campus_dataJSON` = JSON_SET (`campus_dataJSON`, TRIM( '"' FROM JSON_SEARCH(`campus_dataJSON`,'one','Julie')),'MASCOM') 
WHERE 
    `campus_id` = 1 AND 
    JSON_EXTRACT(`campus_dataJSON`,'$.id') = '102';

This returns 0 rows affected... I have heavily googled.

Upvotes: 1

Views: 581

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

Basically you should to use JSON_SET function. For example first question can be solved in next way:

update campus 
 set campus_data = json_set(
   campus_data, '$[2].course', 'MASCOM')
 where campus_id = 1;

Test on SQLize.online

In case when you don't know JSON path you can use JSON_SEARCH & JSON_UNQUOTE like:

update campus 
 set campus_data = json_set(
   campus_data, 
   json_unquote(json_search(campus_data, 'one', 'Eric')), 
   'Erick'
 )
 where campus_id = 1;

Fiddle here

Upvotes: 1

Related Questions