MySQL Update Field With JSON format

Question

How do i update and copy data from the same table but the new data is JSON formatted data.

I am expecting the updated field to be: {"t":"token1", "s":"secret1"}

DB user_api table:

(id) (token) (secret) (api)
---------------------------
 1   token1  secret1  NULL

Update query:

UPDATE user_api SET api = '{"t":"' +token+ '", "s":"' +secret+ '"}';

sidenote: you realize that without a WHERE clause, it will update your entire table. Not a problem if you only have or will always have the one row. — Funk Forty Niner
– Funk Forty Niner, Commented May 6, 2016 at 12:19
MySQL treats + as an addition operator; but has a CONCAT() function that allows you to concatenate strings — Mark Baker
– Mark Baker, Commented May 6, 2016 at 12:21
From a database design prespective: It does not make much sense to concatenate 2 columns into a third column. If some part of your App needs this done, then do it in the PHP when you present or make some use of this concatenation. — RiggsFolly
– RiggsFolly, Commented May 6, 2016 at 12:34
@RiggsFolly thanks for the input. This is only to update existing records. — tonoslfx
– tonoslfx, Commented May 6, 2016 at 13:02

Aju John · Accepted Answer · 2016-05-06 12:22:47Z

4

Use CONCAT

UPDATE user_api SET api = CONCAT('{"t":"', token, '", "s":"', secret, '"}');

answered May 6, 2016 at 12:22

Aju John

2,2441 gold badge14 silver badges27 bronze badges

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

Comments

İsmail Furkan GÖKHASAN · Accepted Answer · 2018-06-03 19:54:08Z

0

Use JSON_SET() function instead.

UPDATE `user_api` SET `api` = JSON_SET(`api`,'$.t',token,'$.s',secret) WHERE ...

This helps you to get rid of all the CONCAT stuff. JSON_SET() function insert the key/value pair if the key is not exists in the specified field. If it exists, then the function update the value.

You can use JSON_INSERT() function if you want to insert if the key doesn't exists. Or you can use JSON_REPLACE() function if you want just update the value if the key exists.

answered Jun 3, 2018 at 19:54

İsmail Furkan GÖKHASAN

431 silver badge9 bronze badges

Collectives™ on Stack Overflow

MySQL Update Field With JSON format

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related