I am using MySQL 8 and my table structure looks as below:
CREATE TABLE t1 (id INT, `group` VARCHAR(255), names JSON);
I am able to correctly insert records using the following INSERT statement:
INSERT INTO t1 VALUES
(1100000, 'group1', '[{"name": "name1", "type": "user"}, {"name": "name2", "type": "user"}, {"name": "techDept", "type": "dept"}]');
The JSON format has two types - user and dept
Now, I have an array of users and depts which looks as below:
SET @userlist = '["user4", "user5"]';
SET @deptlist = '["dept4", "dept5"]';
For a new group group2, I want to insert all those users and depts from within userlist and deptlist array into t1 table using a single query and I have written following query:
SELECT JSON_ARRAY_INSERT(JSON_OBJECT('name', @userlist , 'type', 'user'), ('name', @deptlist , 'type', 'gdl'));
Incorrect parameter count in the call to native function 'JSON_ARRAY_INSERT'