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?

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
– Bill Karwin, Commented Jul 10, 2021 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
– Bill Karwin, Commented Jul 10, 2021 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
– Ramaraju.d, Commented Jul 10, 2021 at 15:10

Bill Karwin · Accepted Answer · 2021-07-10 15:30:37Z

2

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

answered Jul 10, 2021 at 15:30

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ramaraju.d Over a year ago

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 :-)

Collectives™ on Stack Overflow

Parsing JSON objects and storing in mysql variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related