Mysql JSON update key value

Question

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

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"},
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.

Am using MYSQL VERSION 10.2 No such version. Look carefully. — Akina
– Akina, Commented Nov 25, 2020 at 17:04
I have a table like: Add CREATE TABLE & INSERT INTO scripts with this data. — Akina
– Akina, Commented Nov 25, 2020 at 17:05
ok noted for the Create table... doing it As per the Mysql... the CPANEL says Version 10.2 — ErickBest
– ErickBest, Commented Nov 25, 2020 at 17:06
You are probably using mariadb, not mysql. Json_set was added in v10.2.3 - do you have a later version? Pls provide the full error message or detail the unexpected behaviour! Still not succeeded is vague and thus does not tell us anything. — Shadow
– Shadow, Commented Nov 25, 2020 at 17:10
As per the Mysql... the CPANEL says Version 10.2 Execute SELECT VERSION(); and show the output. — Akina
– Akina, Commented Nov 25, 2020 at 17:12

Slava Rozhnev · Accepted Answer · 2020-11-25 17:34:38Z

1

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

edited Nov 25, 2020 at 17:34

answered Nov 25, 2020 at 17:20

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ErickBest Over a year ago

Tested and working .... Just one thing this means I should know the $[N] index all the times.... What if the campus gets over 1000 students...is there away of UPDATING based on "id" ex: Juie's id is 102??

ErickBest Over a year ago

PERFECT.... Exactly what I wanted.... Thank you very much. ACCEPTED

Collectives™ on Stack Overflow

Mysql JSON update key value

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related