Error Code: 1241. Operand should contain 1 column(s)
drop procedure if exists usp_add_actors;
delimiter $$
create procedure usp_add_actors(in jsonText json)
begin
insert into actor(first_name, last_name)
select firstNameArr, lastNameArr
from json_table (jsonText, '$.actors[*]'
columns(
firstNameArr varchar(50) path '$.first_name',
lastNameArr varchar(50) path '$.last_name')
) actorsArray
left outer join actor on first_name = firstNameArr and
last_name = lastNameArr
where (firstNameArr, lastNameArr) is null
union
select firstNameArr, lastNameArr
from json_table(jsonText, '$'
columns (
firstNameArr varchar(50) path '$.actor.first_name',
lastNameArr varchar(50) path '$.actor.last_name'
)
) actorObj
left outer join actor on first_name = lastNameArr and
last_name = lastNameArr
where (lastNameArr, lastNameArr) is null;
end$$
delimiter ;
JSON:
{
"actors": [
{
"first_name": "John",
"last_name": "Doe"
},
{
"first_name": "Johnny",
"last_name": "Depp"
},
{
"first_name": "Al",
"last_name": "Pacino"
},
{
"first_name": "Angelina",
"last_name": "Jolie"
}
],
"actor": {
"first_name": "Chenning",
"last_name": "Tatuum"
}
}
Как это пофиксить?