I got a problem.When insert a int array into Mysql with JSON Type,we can write a sql like INSERT INTO table (json_column) VALUES ('[1,2,3]').But when I want to insert a string array, I used INSERT INTO table (json_column) VALUES ("['item1','item2','item3']"), I was prompted with a error: 3140 - Invalid JSON text: "Invalid value." at position 1 in value for column 'test.names'.
What should i do?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
JSON documents have specific rules about format. String values inside a JSON document must be delimited with double-quotes, not single-quotes.
WRONG:
"['item1','item2','item3']"
RIGHT:
'["item1","item2","item3"]'
This works nicely inside SQL strings, because SQL strings should be delimited with single-quotes. MySQL is non-standard in supporting double-quotes as an alternative string delimiter.
To ensure creating a JSON document with correct format, you might like to use the JSON_ARRAY() or JSON_OBJECT() functions. You give arguments as individual values, and the function will return a document value, guaranteed to be formatted as valid JSON.
INSERT INTO table (json_column)
VALUES (JSON_ARRAY('item1', 'item2', 'item3'));Method 2
Make it more simpler like
INSERT INTO table (json_column) VALUES ('["item1","item2","item3"]')Or use key-value pairs like
INSERT INTO table VALUES ('{"name": "item1", "id": "item2", "mob": "item3"}');All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0