CODEWITHSUNDEEP
mysqlnode.jsjsonsql-update
user3596335
user3596335

Reputation:

Mysql - update + insert json

I got a table that has a JSON field. The default value for the field is "NULL" - now I'd like to update a single field of the JSON data.

   | ----------------- |
   | [int]  |   [JSON] |
   |  xy    |   ipdata |
   | ----------------- |

So the field could be something like this:

{ ip: "233.233.233.233", "data": "test", "name": "Peterson", "full_name": "Hanson Peterson" }

So I'd like to update the IP.

update table set ipdata = JSON_SET(ipdata, "$.ip", "newIp") where xy = 2;

But what happens if the field is NULL? The query above does not seems to "create" a new JSON with just the field IP. It just does nothing.

How can I tell mySql to insert the {"ip": "newIp"} if the field is empty and otherwise just update the ip json key?

Upvotes: 1

Views: 417

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use Case .. When to handle Null. When the field is null, you can instead create Json_object() and set it:

UPDATE table 
SET ipdata = CASE WHEN ipdata IS NULL THEN JSON_OBJECT("ip", "newIp") 
                  ELSE JSON_SET(ipdata, "$.ip", "newIp") 
             END 
WHERE xy = "xy";

Upvotes: 2

Related Questions