Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to fill fill json field with result set data?

On mysql 8 site I need to collect data in temp table for tasks assigned to user and I want in json field to keep id, title of related tasks :

create procedure sp_fill_user_tasks_report( OUT out_returnCode bigint unsigned )
BEGIN
    DECLARE userId int unsigned;
    DECLARE userName varchar(100);
    DECLARE userEmail varchar(100);
    DECLARE activeTasksCount int unsigned;
    DECLARE completedTasksCount int unsigned;

    DECLARE done INT DEFAULT FALSE;
    DECLARE usersCursor cursor
        for SELECT users.id, users.name, users.email
            FROM users
            ORDER BY users.id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    CREATE TEMPORARY TABLE IF NOT EXISTS tempUserTasksReport
    (
        user_id        bigint unsigned,
        user_name      varchar(100),
        user_email      varchar(100),
        active_tasks_count int unsigned,
        completed_tasks_count int unsigned,
        assigned_tasks json DEFAULT NULL
    );

    DELETE FROM tempUserTasksReport;
    call sp_clear_debug();

    SET out_returnCode := 0;

    open usersCursor;
    usersLoop:
    loop
        -- get all children of in_taskId and check them
        fetch usersCursor into userId, userName, userEmail;
        IF done THEN
            LEAVE usersLoop;
        END IF;

        select count(*) into activeTasksCount from users
            where userId  in (select distinct task_user.user_id from task_user, tasks where tasks.status = 'T' and task_user.task_id = tasks.id);

        select count(*) into completedTasksCount from users
            where userId  in (select distinct task_user.user_id from task_user, tasks where tasks.status = 'D' and task_user.task_id = tasks.id);

        -- I try to fill assigned_tasks field with result set - but got error
        INSERT INTO tempUserTasksReport SELECT userId, userName, userEmail, activeTasksCount, completedTasksCount, JSON_SET(assigned_tasks, '"NotSureWhatIsItFor"',
            (select task_user.task_id, tasks.title from task_user, tasks where ask_user.user_id = userId and task_user.task_id = tasks.id ));

        SET out_returnCode := out_returnCode + 1;

    end loop usersLoop;
    close usersCursor;
END;

But running the procedure got error :

SQL Error [1054] [42S22]: Unknown column 'assigned_tasks' in 'field list'

assigned_tasks that is json field in my declare local temporary table…

How to fix it ?

8.0.34-0ubuntu0.22.04.1

Thanks in advance!
Tags:

2

Answers


  1. 0

    You use the next statement (formatted) :

    -- I try to fill assigned_tasks field with result set - but got error

INSERT INTO tempUserTasksReport 
SELECT userId, 
       userName, 
       userEmail, 
       activeTasksCount, 
       completedTasksCount, 
       JSON_SET( assigned_tasks, 
                 '"NotSureWhatIsItFor"',
                 ( select task_user.task_id, 
                          tasks.title 
                   from task_user, tasks 
                   where ask_user.user_id = userId and task_user.task_id = tasks.id 
                   )
                 );

    This statement uses SELECT sub-statement without FROM clause which uses the next function call:

    JSON_SET( assigned_tasks, 
          '"NotSureWhatIsItFor"',
          { subquery}
          )

    So assigned_tasks is treated as local variable name. But you have not declared such variable which results in error.

    PS. The columns of the table which you INSERT INTO are not visible in SELECT part which have its own datasource clause.

    PPS. You must use JSON_OBJECT() or JSON_ARRAY(), maybe nested, it seems.

    Login or Signup to reply.
  2. 0

    You should construct the json object separately and then insert it into the tempUserTasksReport table.

    Replace this line..

     -- I try to fill assigned_tasks field with result set - but got error
INSERT INTO tempUserTasksReport SELECT userId, userName, userEmail, activeTasksCount, completedTasksCount, JSON_SET(assigned_tasks, '"NotSureWhatIsItFor"',
(select task_user.task_id, tasks.title from task_user, tasks where ask_user.user_id = userId and task_user.task_id = tasks.id ));

    Into this line..

    -- Construct the JSON object
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', task_user.task_id, 'title', tasks.title)) INTO taskJson
FROM task_user, tasks
WHERE task_user.user_id = userId AND task_user.task_id = tasks.id;

-- Insert into the temporary table
INSERT INTO tempUserTasksReport (user_id, user_name, user_email, active_tasks_count, completed_tasks_count, assigned_tasks)
VALUES (userId, userName, userEmail, activeTasksCount, completedTasksCount, taskJson);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search