How to insert a string array into mysql json column-CodePudding

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?

CodePudding user response：

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"}');

CodePudding user response：

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'));