FUNCTION RETURNS TABLE - SQL Server to MySQL Migration

In SQL Server a user-defined function (UDF) can return a table (a table-valued function), so you can refer to the function in FROM clause.

MySQL does not support table-valued functions but you can use JSON arrays and JSON functions to implement them.

SQL Server:

  -- A table-valued function
  CREATE FUNCTION getColors()
  RETURNS TABLE
  AS
  RETURN (
    SELECT 'Red' AS name, 'R' AS category
    UNION ALL
    SELECT 'Blue' AS name, 'B' AS category
    UNION ALL
    SELECT 'Green' AS name, 'G' AS category
  )
  GO
 
  -- Use the function in FROM
  SELECT * FROM getColors()

Result:

In MySQL you can use JSON_ARRAYAGG and JSON_OBJECT functions to create a JSON array returned by a function:

MySQL:

  DELIMITER //
 
  -- Emulate table-valued function by returning JSON array
  CREATE FUNCTION getColors()
  RETURNS LONGTEXT
  DETERMINISTIC
  BEGIN
    DECLARE data LONGTEXT;
    SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'category', category))  INTO data 
    FROM
     (
       SELECT 'Red' AS name, 'R' AS category
       UNION ALL
       SELECT 'Blue' AS name, 'B' AS category
       UNION ALL
       SELECT 'Green' AS name, 'G' AS category
    ) data;
    RETURN data;
  END
  //
 
  DELIMITER ;

Now you can invoke this function using JSON_TABLE function as follows:

MySQL:

  -- Using JSON_TABLE function to convert JSON array back to rows
  SELECT *
  FROM JSON_TABLE(getColors(), '$[*]'
             COLUMNS(name VARCHAR(30) PATH '$.name', 
                             category VARCHAR(30) PATH '$.category')) t;

Result:

For more information, see SQL Server to MySQL Migration.