CODEWITHSUNDEEP
mysqljsondatabase
VanDeath
VanDeath

Reputation: 529

Data Updating of JSON doesn't work (MySQL)

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

Category

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 JSON_INSERT 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 JSON_SET it just replaces my existing languages. I also tried JSON_ARRAY_APPEND But then it looks like this: 

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

Does anyone know the right MYSQL command ?

Upvotes: 0

Views: 119

Answers (1)

Barmar
Barmar

Reputation: 781068

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.

Upvotes: 1

Related Questions