Append a string value to an exisiting json value in mysql

Question

I'm new to MySQL. I'm trying to add a string value to a json value in MySQL. The column name is IPConfig. This is the current json string in the column.

{"theme":"black", "button1link":"http//sample.com", "name":"pp"}

I have to append a "www" to button1link value.

Thanks in advance!

Rahul · Accepted Answer · 2016-12-30 05:51:43Z

3

Here you can try

UPDATE table SET DATA= JSON_SET(DATA, "$.button1link", INSERT("http//sample.com", 7, 0,'www')) WHERE 1 = 1;

But for this to work, you will need MySQL 5.7+

You can have insert function docs here.

answered Dec 30, 2016 at 5:51

Rahul

18.6k7 gold badges43 silver badges64 bronze badges

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

11 Comments

Pranay Pant Over a year ago

Hey thanks for the quick reply. So i have a number of these values where i want to insert 'www' after 'https://' but the string after that is different. what would be a generalized solution for that. Thanks for your time!

Rahul Over a year ago

It will just need http:// or http// or https:// or https//, not string after that. check these number of characters used, e.g. for https:// , write 9,0 instead of 7,0. It wont check for anything after https://, so no need to worry. For more details check insert function docs

Pranay Pant Over a year ago

awesome thanks! is it also possible to edit multiple columns in the same query for the same purpose. Like suppose there is a another json key like button2link.

Rahul Over a year ago

I am sure you know how to write query to update multiple columns :)

Pranay Pant Over a year ago

UPDATE creative SET IpConfig = JSON_SET(IpConfig, '$.button1Link', INSERT('https://', 9, 0,'www.')) WHERE id = 1609606 ;

|

Collectives™ on Stack Overflow

Append a string value to an exisiting json value in mysql

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related