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?

Try with JSON_MERGE_PATCH , dev.mysql.com/doc/refman/5.7/en/… — Mate
– Mate, Commented Mar 14, 2022 at 21:21

SOS · Accepted Answer · 2022-03-15 18:18:56Z

6

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

edited Mar 15, 2022 at 18:18

answered Mar 14, 2022 at 22:17

SOS

6,5602 gold badges14 silver badges29 bronze badges

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

2 Comments

AbuMariam Over a year ago

Thanks for your answer but I wanted the emailAddress property to be inside the nested 'user' object as I had in the OP above. Could we do that with JSON_INSERT()?

SOS Over a year ago

Sorry, my bad. Just change the path to $.user.emailAddress.

Collectives™ on Stack Overflow

Append property to JSON object in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related