0

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
  • 1
    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. – Akina Mar 12 '19 at 8:25
  • {"/users/57/user-11.png"} is invalid JSON – a_horse_with_no_name Mar 12 '19 at 8:26
  • @a_horse_with_no_name missing a ";" or something? – belgiums Mar 12 '19 at 8:35
  • See The JSON Data Type for correct JSON literal format. – Akina Mar 12 '19 at 8:47
  • is this valid? No. Read user manual please. – Akina Mar 12 '19 at 10:06
0

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 |
+----+-------+-----------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.