Append property to JSON object in MySQL

user_3223841 0
Viewed 145

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":"joeR@gmail.com"
  },
  "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","joeR@gmail.com")) where id = 4783

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

mysql json
1 Answers

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'
                           , 'joeR@gmail.com')
 WHERE  id = 4783

Results:

{
  "city": "Salisbury",
  "user": {
    "lastName": "Rizzo",
    "firstName": "Joe",
    "emailAddress": "joeR@gmail.com"
  }
}

db<>fiddle here

user_88952920
Related