CODEWITHSUNDEEP
mysql
lStoilov
lStoilov

Reputation: 1339

MySQL JSON_REPLACE - if key does not exist create key

I am using JSON_REPLACE to update the value of a key, but I am having a hard time figure out how to create the key before updating it if it does not exist.

This is how my query looks like.

$query_update = "UPDATE `forms` SET `conf` = JSON_REPLACE(`conf`, '$.title', '$title') WHERE id='32'";

Any idea how can I do that in the same query?

UPDATE Thanks to the comments by Juan Eizmendi and Salman A below, changing JSON_REPLACE to JSON_SET resolved the problem.

The updated query looks like that

$query_update = "UPDATE `forms` SET `conf` = JSON_SET(`conf`, '$.title', '$title') WHERE id='32'";

Upvotes: 0

Views: 1227

Answers (1)

Juan Eizmendi
Juan Eizmendi

Reputation: 1134

You can use JSON_SET instead of JSON_REPLACE, it will insert the key if it doesn't exist or update an existing one:

SELECT JSON_SET('{}', '$.title', 'new title');
-- {"title": "new title"}

SELECT JSON_SET('{"title":"old title"}', '$.title', 'new title');
-- {"title": "new title"}

Upvotes: 3

Related Questions