MySQL convert multiple columns into JSON

Question

I'm trying to convert multiple columns from one table into single JSON in another table in mysql database (version 5.7.16). I want use SQL query.

First table look like this

   CREATE TABLE `log_old` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `temperature` DECIMAL(5,2) NULL DEFAULT NULL,
    `heating_requested` BIT(1) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
   )COLLATE='utf8_general_ci'
   ENGINE=InnoDB;

Second table look like this

    CREATE TABLE `log_new` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    'data' JSON,
    PRIMARY KEY (`id`),
    )COLLATE='utf8_general_ci'
    ENGINE=InnoDB;

data JSON has same format in all rows of log_new table, it should look like this

{
    temperature: value,
    heatingRequested: false
}

for example log_old look like this

 +--+-----------+-----------------+
 |id|temperature|heating_requested|
 +--+-----------+-----------------+
 |1 |    12     |        true     |
 +--+-----------+-----------------+
 |2 |    14     |        true     |
 +--+-----------+-----------------+
 |3 |    20     |        false    |
 +--+-----------+-----------------+

and I want to log_new looked like this

 +--+-----------------------------------------+
 |id|              data                       |
 +--+-----------------------------------------+
 |1 |{temperature:12, heatingRequested: true} | 
 +--+-----------------------------------------+
 |2 |{temperature:14, heatingRequested: true} | 
 +--+-----------------------------------------+
 |3 |{temperature:20, heatingRequested: false}| 
 +--+-----------------------------------------+

I tried to use JSON_INSERT()

    SELECT JSON_INSERT((SELECT data  FROM log_new  ), '$.temperature',
   (SELECT temperature FROM log_old));

but this throw error "subquery returns more than 1 row" I came with only solutions thats work to use while and do it row by row but this can take long time

   DELIMITER //  
   CREATE PROCEDURE doLog()
   BEGIN
    SELECT COUNT(*) into @length from log_zone;  
    SET @selectedid = 1;
    WHILE @selectedid < @length DO
        SELECT temperature,heating_requested INTO @temperature,@heating_requested FROM log_old where id=@selectedid;    
        SELECT   JSON_OBJECT('temperature',@temperature,'heatingRequested',@heating_requested) into @data_json;
        SET @selectedid = @selectedid + 1;
        INSERT INTO log_new (data) VALUES (@data_json);
     END WHILE;
    END;
    //
    CALL doLog()

Answer 1

As all your data are available on single lines, you don't need to use subqueries or loops to build the json object.

You can try something like :

INSERT INTO log_new (data)
   SELECT json_object('temperature',log_old.temperature,'heatingRequested',log_old.heating_requested) 
   FROM   log_old