How to insert a string array into mysql json column

Question

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?

Convert to json and insert into one column or insert in multiple columns — Vivek Nuna
– Vivek Nuna, Commented Oct 9, 2021 at 14:09

Bill Karwin · Accepted Answer · 2021-10-09 16:16:54Z

7

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

answered Oct 9, 2021 at 16:16

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

Hobo Over a year ago

Thank you very much. The statement '["item1","item2"]' is available

Luxior May 26 at 9:08

Just to note, nowadays inserting '["item1","item2","item3"]' automatically escape strings and thus does not generate the same JSON values as using JSON_ARRAY(). I highly suggest using JSON_ARRAY()

Subhashis Pandey · Accepted Answer · 2021-10-09 13:49:18Z

0

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

answered Oct 9, 2021 at 13:49

Subhashis Pandey

1,5381 gold badge13 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to insert a string array into mysql json column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related