Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
It is a table-valued function that converts a MPSQL array to a set of rows.
TABLE(array)
array
: A MPSQL array of scalar values.A set of table-type rows.
table_col
, with one row for each array entry.TABLE()
function can be used only in the FROM
clause of a query.TABLE()
function must contain only scalar values of valid column data types.RECORDS
and multi-directional arrays are not supported as arguments to the TABLE()
function.FROM
clause (with an argument that is correlated), if the array value passed to TABLE()
has N entries, the output will have N * number_of_values_from_the_table_on_the_left rows. The values from the table to the left will be repeated for each array entry.SELECT * FROM TABLE(JSON_TO_ARRAY('[1,2,3]'));
****
+------------+
| table_col |
+------------+
| 1 |
| 2 |
| 3 |
+------------+
In the FROM
clause, if the table on the left has 2 rows and the array passed to TABLE()
has 2 entries, the output will have 4 rows (2*2). For example,
SELECT * FROM num, TABLE(JSON_TO_ARRAY('[1,2]'));
****
+------+-----------+
| x | table_col |
+------+-----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
+------+-----------+
The following example demonstrates the usage of TABLE()
function through UDFs.
CREATE TABLE square
(num INT PRIMARY KEY);
INSERT INTO square VALUES (1),(2),(3),(4),(5);
DELIMITER //
CREATE OR REPLACE FUNCTION to_array (x INT)
RETURNS ARRAY(INT) AS
DECLARE
arr ARRAY(INT) = CREATE_ARRAY(x+1);
BEGIN
FOR i IN 0..x LOOP
arr[i] = i * i;
END LOOP;
RETURN arr;
END //
DELIMITER ;
SELECT num, table_col AS "SQUARE"
FROM square INNER JOIN TABLE(to_array(6))
ON table_col = num*num ORDER BY num;
****
+-----+--------+
| num | SQUARE |
+-----+--------+
| 1 | 1 |
| 2 | 4 |
| 3 | 9 |
| 4 | 16 |
| 5 | 25 |
+-----+--------+
The TABLE
function can be used to expand a field in a table. For example,
SELECT * FROM empRole;
****
+-------+-----------------------------------+
| Name | Role |
+-------+-----------------------------------+
| Adam | ["Team Lead","Security Engineer"] |
| Mark | ["Product Manager"] |
+------+------------------------------------+
SELECT Name, table_col AS "Title" FROM empRole
JOIN TABLE(JSON_TO_ARRAY(Role));
****
+------+---------------------+
| Name | Title |
+------+---------------------+
| Adam | "Team Lead" |
| Adam | "Security Engineer" |
| Mark | "Product Manager" |
+------+---------------------+