mysqljsonstored-functions

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


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;
$$;

Solution

  • 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