MySQL JSON_REPLACE - if key does not exist create key

Question

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'";

Sure, but how can I check if the key exists in the same query? — lStoilov
– lStoilov, Commented Jul 28, 2021 at 8:16
So you want to update the key if exists, if it doesn't create key/value pair, that's what JSON_SET does. if you only want to check for a key determine if a json value — Juan Eizmendi
– Juan Eizmendi, Commented Jul 28, 2021 at 8:25
Yes, I want to first check if the pair exist and if yes update, else create. But don't know how to combine both JSON_REPLACE and JSON_SET — lStoilov
– lStoilov, Commented Jul 28, 2021 at 9:19

Salman Arshad · Accepted Answer · 2021-07-28 20:58:14Z

3

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

edited Jul 28, 2021 at 20:58

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

answered Jul 28, 2021 at 18:49

Juan Eizmendi

1,1649 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

MySQL JSON_REPLACE - if key does not exist create key

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related