Parsing JSON objects and storing in mysql variables

Question

I am trying to parse a json array and its objects to further make use of them in my mysql procedure.

But while reading the values it shows null. _list will contain

[{"floor_id":"5","length":"40"},{"floor_id":"6","length":"61"}]

Code:

     CREATE DEFINER=`admin`@`%` PROCEDURE `manage_room`(IN _id INT,
                                                        IN _list JSON,                                                    
                                                        INOUT v_message varchar(1000),
                                                        INOUT v_code int
                                                           
                                                        )
    BEGIN
    declare _floor_number int ;
declare floor_list_length int;
set floor_list_length =3;
                            iterator:
                            LOOP  
                                IF floor_list_length = 0 OR floor_list_length IS NULL THEN
                                    LEAVE iterator;
                                END IF;  
                                select JSON_EXTRACT(_rooms_list,CONCAT('$._list[', `floor_list_length`, '].floor_id')) as f ;
                                select JSON_EXTRACT(_rooms_list,CONCAT('$._list[', `floor_list_length`, '].length')) as l ;
                                insert into temp(val,txt) values (f,l);
            end loop;
    
    end

Can someone correct me what mistake i am doing?

Try: `SELECT JSON_EXTRACT(\`_list\`, '$[0].floor_id') as f ;`. — wchiquito, Jul 10 '21 at 14:42
With respect, there are so many mistakes I don't know where to begin. The input parameter is `_list` but you reference it as `_rooms_list`. Then you use `$._list` in the JSON path, but that key doesn't appear in the JSON content. You declare a variable `_floor_number` but you do not use it. Then you use a variable `floor_list_length` without declaring it or setting a value. You try to select fields from the JSON with aliases but you don't set them to any variable. Then you use `f` and `l` as if they are variables. This procedure is totally gibberish. — Bill Karwin, Jul 10 '21 at 14:42
You edited your question to add the declaration of `_floor_number` and set it to 3. But there are still many other problems. — Bill Karwin, Jul 10 '21 at 14:49
@BillKarwin Sorry, that was not the complete code. My thought was to give an idea what I am trying to achieve in the program. I should have given complete code :-( — Ramaraju.d, Jul 10 '21 at 15:10

score 2 · Answer 1 · answered Jul 10 '21 at 15:30

I think what you mean to do is to insert multiple rows to the table temp corresponding to the array in your JSON input document. Each member of the JSON array has a pair of fields, and these two fields should be set to the columns val and txt in the temp table.

CREATE DEFINER=`admin`@`%` PROCEDURE `manage_room`(
  IN _id INT,
  IN _list JSON,                                                    
  INOUT v_message varchar(1000),
  INOUT v_code int
)
BEGIN
  DECLARE floor_list_length INT;
  DECLARE i INT DEFAULT 0;
  DECLARE element JSON;

  SET floor_list_length = JSON_LENGTH(_list);

  WHILE i < floor_list_length DO
    SET element = JSON_EXTRACT(_list, CONCAT('$[',i,']'));

    INSERT INTO temp
    SET val = JSON_EXTRACT(element, '$.floor_id'),
        txt = JSON_EXTRACT(element, '$.length');

    SET i = i + 1;
  END WHILE;
END

But here's an even simpler solution, assuming you use MySQL 8.0. Make the JSON into a set of rows using JSON_TABLE(), then insert that set directly into the temp table.

CREATE DEFINER=`admin`@`%` PROCEDURE `manage_room`(
  IN _id INT,
  IN _list JSON,                                                    
  INOUT v_message varchar(1000),
  INOUT v_code int
)
BEGIN
  INSERT INTO TEMP (val, txt)
  SELECT j.floor_id, j.length FROM JSON_TABLE(
    _list, '$[*]' COLUMNS(
      floor_id INT PATH '$.floor_id',
      length INT PATH '$.length'
    )
  ) AS j;
END

Thanks a lot. I was able to solve it using the syntax mentioned by wchiquito. Your code have some more information on different ways to extract the values. I am fiddling with the documentation as well. Thanks for your patience :-) — Ramaraju.d, Jul 10 '21 at 16:40

Parsing JSON objects and storing in mysql variables

1 Answers1