Mysql - update + insert json

Question

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?

Madhur Bhaiya · Accepted Answer · 2018-10-30 09:32:08Z

2

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

edited Oct 30, 2018 at 9:32

answered Oct 30, 2018 at 9:11

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

Mysql - update + insert json

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related