create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;create definer = `seongho.park` function json_array_cast(arr json, type varchar(255) ) returns json
sql security invoker
BEGIN
SET @arr = arr;
set @type = upper(type);
SET @idx = json_length(@arr);
while @idx >= 0 do
if @type = 'CHAR' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as char);
elseif @type = 'SIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as signed);
elseif @type = 'UNSIGNED' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as UNSIGNED);
elseif @type = 'DATE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATE);
elseif @type = 'DATETIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DATETIME );
elseif @type = 'TIME' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as TIME);
elseif @type = 'DECIMAL' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DECIMAL);
elseif @type = 'DOUBLE' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as DOUBLE);
elseif @type = 'BINARY' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as BINARY );
elseif @type = 'JSON' then
SET @itm = cast(JSON_EXTRACT(@arr, concat('$[',@idx, ']')) as JSON);
else
SET @itm = JSON_EXTRACT(@arr, concat('$[',@idx, ']'));
end if;
set @arr = json_replace( @arr, CONCAT('$[', @idx,']'), @itm);
set @idx = @idx - 1;
end while;
return cast(@arr as json);
end;CREATE definer=`seongho.park` FUNCTION JSON_UNIQ(arr JSON) RETURNS json
sql security invoker
BEGIN
SET @arr = arr;
SET @loop_index = JSON_LENGTH(@arr);
set @rand_idx = FLOOR( (RAND() * @loop_index));
set @is_integer = 'INTEGER' = json_type(json_extract(@arr,concat('$[',@rand_idx,']')));
if @is_integer then
set @arr = json_array_cast(@arr, 'char');
end if;
WHILE @loop_index >= 0 DO
SET @item = JSON_UNQUOTE(JSON_EXTRACT(@arr, concat('$[',@loop_index,']')));
SET @itemcount = coalesce(JSON_LENGTH(JSON_SEARCH(@arr, 'all', @item)), 0);
IF @itemcount > 1 OR @item = 'null' THEN
SET @arr = JSON_REMOVE(@arr, CONCAT('$[',@loop_index,']'));
END IF;
SET @loop_index = @loop_index - 1;
End WHILE;
if @is_integer then
set @arr = json_array_cast(@arr, 'signed');
end if;
RETURN CAST(@arr AS JSON);
END;
select
f.id
from nuvi_foods as f
inner join departments as d on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2 or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
and rh.id not in (
select id from recommendation_histories where nuvi_user_id = :user_id
)
group by f.food_name;
select f.id
from nuvi_foods as f
join departments as d
on f.department_id = d.id and (d.type = '어린이집' or d.type = '유치원')
-- left outer join recommendation_histories rh on f.id = rh.nuvi_food_id
where f.nuvi_food_main_category_id = 3
and (f.food_group_id = 2
or f.food_group_id = 3)
and (d.type = '어린이집' or d.type = '유치원')
and not (REGEXP_LIKE(food_name, '[-0-9\`~!@#$%^&*(-)-_;,.<>/?=+"|\\']'))
-- and rh.id not in (select id
-- from recommendation_histories
-- where nuvi_user_id = :user_id)
group by f.food_name
;`;