MySQL insert JSON data type in existing row

Question

I have a column in my MySQL table that is type json. I am trying to update a row with a SQL script.

This is the JSON I am attempting to insert into the column

{
  "HE": [{
    "isbn": "1234",
    "type": "Some text"
  }],
  "HS": []
}

I have tried quite a few different ways with no luck, below is the method of updating a row I am most familiar with.

UPDATE `textbooks` 
SET `isbns` = {"HE":[{"isbn":"9781337763639","type":"Single Term HW & 
eBook University"}],"HS":[]} 
WHERE `id` = 939;

Barmar · Accepted Answer · 2017-04-12 20:28:43Z

2

The JSON string has to be in quotes. Also, it shouldn't have newlines in it.

UPDATE `textbooks` 
SET `isbns` = '{"HE":[{"isbn":"9781337763639","type":"Single Term HW & eBook University"}],"HS":[]}'
WHERE `id` = 939;

answered Apr 12, 2017 at 20:28

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

2 Comments

question in similar context, what if I would like to set isbn=id+something. In that case, how can I set any field in the json with variable params?

See the functions for modifying JSON values here