Mysql JSON_SET ,JSON_INSERT & JSON_REPLACE difference

HOw to read JSON key:

CREATE TABLE json_data ( city JSON );

insert into json_data(city) values('{"name":"mumbai"}');

insert into json_data(city) values('{"name":"pune"}');

insert into json_data(city) values('{"name":"kolhapur"}');

select city->'$.name' from json_data;

Output:

+----------------+

| city->'$.name' |

+----------------+

| "mumbai" |

| "pune" |

| "kolhapur" |

+----------------+

 create table user_info(

id int auto_increment primary key,

info json

)

insert into user_info(id,info) values(1,'{"firstName":"sangram","lastName":"desai"}')

select id,JSON_EXTRACT(info,'$.firstName'), JSON_EXTRACT(info,'$.lastName') from user_info;

JSON_SET:

replaces existing values and adds nonexisting values

select id,JSON_SET(info,'$.middleName','shivaji') from user_info;

above query add new value middleName

select id,JSON_SET(info,'$.firstName','swapnil') from user_info;

above query replace existng value of firstName

JSON_INSERT:

JSON_INSERT do not replace existing value but add new

Queries:

select id,JSON_INSERT(info,'$.firstName','sagar') from user_info;

above query will not replace existing value of firstName

select id,JSON_INSERT(info,'$.middleName','shiva') from user_info;

above query will add new value for middleName

JSON_REPLACE:

replaces only existing values.

SELECT JSON_REPLACE(info, '$.firstName', 'sagar') from user_info;

above query replace value of firstName from sangram to sagar

SELECT JSON_REPLACE(info, '$.middleName', 'sagar') from user_info;

Here middleName key does not existing in column so it will not replace or add new key