change mysql datatype column from string to json

Question

I have an database column "image" with links to the image of the user on the server. In the next update I need to obtain multiple images, so I'm trying to change the datatype of the column to JSON.

I already put a {" before and "} after the strings. So now the values of the column looks like:

{"/users/57/user-11.png"}

When I try to update the table by:

ALTER TABLE ah_website_dev2.vendor_horses MODIFY COLUMN image json;

I get following error message:

Error Code: 3140. Invalid JSON text: "Missing a colon after a name of object member." at position 27 in value for column '#sql-1d30_44.image'. 0.011 sec

• Akina: Create new field of JSON datatype. Update this field from old (image) field using proper function(s). Check the result. Drop old field, rename new.
• user1822: {"/users/57/user-11.png"} is invalid JSON
• sg_sg94: @a_horse_with_no_name missing a ";" or something?
• Akina: See The JSON Data Type for correct JSON literal format.
• Akina: is this valid? No. Read user manual please.

Answer 1

Whilst a JSON field may do what you want, consider proper Data Normalisation rules first.

As soon as you want "more than one" of something, you should be thinking about a new table that contains those multiple instances, with a foreign key back to the table that contained "the one" thing previously.

select * from vendor_horses ; 
+----+---------+
| id | name | 
+----+---------+
| 1 | Red Rum |
+----+---------+
select * from vendor_horse_images ; 
+----+-------+-----------------------+
| id | vh_id | path | 
+----+-------+-----------------------+
| 22 | 1 | /users/57/user-11.png |
| 33 | 1 | /users/57/user-73.png |
+----+-------+-----------------------+

Answer 2

{"/users/57/user-11.png"} is not valid json.

You could try something like {"link":"/users/57/user-11.png"}.

However, the json data type looks an odd choice for such values. A correct (and basic) way to do this would be to create a table "links" with one column ID and one string column, and then use vendor_horse_images.path as a FK referencing the path IDs in the new table