Is it possible to make a function that receives a JSON parameter in MySQL?-CodePudding

I would like to know if it is possible to make a function that receives a JSON parameter in MySQL. Something like it is done in PostgreSQL (attached example).

I need to migrate a PostgreSQL DB to MySQL and there are many such functions.

CREATE OR REPLACE FUNCTION fn_crud_sls_quotation_concept_types (data json)

RETURNS boolean
LANGUAGE 'plpgsql' 
AS 
$$

DECLARE
"vType"         integer := data ->> 'type';
"vId"           bigint  := data ->> 'id';
"vDescription"  varchar := data ->> 'description';
"vStatus"       integer := data ->> 'status';
"vUserId"       integer := data ->> 'userId';

BEGIN

IF "vType" = 1 THEN -- Data Insert  

        INSERT INTO tbl_sls_quotation_concept_types(description, status, "createdBy")
        VALUES("vDescription", "vStatus", "vUserId");
        
    RETURN TRUE;
    
ELSIF "vType" = 2 THEN -- Data Update

        UPDATE tbl_sls_quotation_concept_types 
        SET "description"   = "vDescription",
            "status"        = "vStatus",
            "updatedBy"     = "vUserId", 
            "updatedAt"     = current_timestamp
        WHERE id = "vId";
    
    RETURN TRUE;
    
ELSIF "vType" = 3  THEN -- Data Delete
        
        UPDATE tbl_sls_quotation_concept_types 
        SET status = 0 ,
            "updatedBy" = "vUserId", 
            "updatedAt" = current_timestamp
        WHERE id = "vId" ;
        
    RETURN TRUE;

END IF;

END;
$$;

CodePudding user response：

Yes, a stored function can accept an input parameter of the JSON data type:

mysql> delimiter $$
mysql> create function myfunc(data json) returns varchar(10) deterministic 
  begin 
    declare val varchar(10); 
    set val = json_extract(data, '$.val'); 
    return val; 
  end$$
mysql> delimiter ;
mysql> select myfunc('{"foo": 1, "val": "bar"}');
 ------------------------------------ 
| myfunc('{"foo": 1, "val": "bar"}') |
 ------------------------------------ 
| "bar"                              |
 ------------------------------------

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html says:

type:

Any valid MySQL data type