VanDeath VanDeath - 1 year ago 50
JSON Question

Data Updating of JSON doesn't work (MySQL)

So I have a Table in a Database that looks like this:


idCategory | data

the "idCategory" column is AUTO_INCREMENT and the data field is a JSON.
I have data stored in the table looking like this:

1, {"CategoryNames": {"DE": "Wirtschaft", "EN": "Economy"}}
2, {"CategoryNames": {"DE": "Geschichte", "EN": "History"}}

The Idea is, to Store the name for a Category in different Languages. I preferably need the "CategoryNames" tag, so I can add other Properties like "Difficulty" or so, later without affecting the languages.

What I want to do, is to add other Languages under the Tag "CategoryNames" without deleting or Replacing the existing ones.

So it would look somthething like this

2, {"CategoryNames": {"DE": "Geschichte", "EN": "History", "FR" : "Histoire"}}

I have tried it with
with something like this:

UPDATE phpframework.category SET data = JSON_INSERT(data, "$.CategoryNames", JSON_OBJECT('FR', 'Histoire')) WHERE idCategory = 2;

But nothing happens, the Column remains the same. With
it just replaces my existing languages. I also tried
But then it looks like this:

{"CategoryNames": [{"DE": "Geschichte", "EN": "History"}, {"EN": "Math"}]}

Does anyone know the right MYSQL command ?

Answer Source

You don't want to insert a whole object, you want to set a property in an existing object.

UPDATE phpframework.category 
SET data = JSON_INSERT(data, "$.CategoryNames.FR", 'Histoire') 
WHERE idCategory = 2;

You could also use JSON_SET instead of JSON_INSERT if you want it to replace a property that already exists.