Append property to JSON object in MySQL

Question

I have a table in MySQL whose column user_json holds a JSON value like this..

{
  "user": {
    "firstName": "Joe",
    "lastName":"Rizzo"
  },
  "city":"Salisbury"
}

I want to add an email address property to the existing JSON so the result should look like..

{
  "user": {
    "firstName": "Joe",
    "lastName":"Rizzo",
    "emailAddress":"[email protected]"
  },
  "city":"Salisbury"
}

I thought I could use JSON_INSERT like so....

update my_table set user_json = 
JSON_INSERT(user_json, '$. user', JSON_OBJECT("emailAddress","[email protected]")) where id = 4783

But this didn't update the JSON. Is there any other way to accomplish this?

Answer 1

Updated 2022-03-15

I believe JSON_INSERT() accepts a key-value pair (where the path is the key). So you don't need JSON_OBJECT(). Instead try:

 UPDATE YourTable  
 SET    user_json = JSON_INSERT(user_json
                           , '$.user.emailAddress'
                           , '[email protected]')
 WHERE  id = 4783

Results:

{
  "city": "Salisbury",
  "user": {
    "lastName": "Rizzo",
    "firstName": "Joe",
    "emailAddress": "[email protected]"
  }
}

db<>fiddle here