It is a table-valued function that converts a MPSQL array to a set of rows.

Syntax

TABLE(array)

Arguments

• array: A MPSQL array of scalar values.

Return Type

A set of table-type rows.

Remarks

• It returns a single column of output named table_col, with one row for each array entry.
• The TABLE() function can be used only in the FROM clause of a query.
• The array argument to the TABLE() function must contain only scalar values of valid column data types.
• Array of RECORDS and multi-directional arrays are not supported as arguments to the TABLE() function.
• MPSQL arrays are not supported as a table data type or array literals in SingleStore DB queries.
• When used as a reference to the second (or subsequent) table in the 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.

Examples

Example 1

SELECT * FROM TABLE(JSON_TO_ARRAY('[1,2,3]'));
****
+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

Example 2

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,

CREATE TABLE num (x int);
INSERT INTO num VALUES (1),(2);
SELECT * FROM num, TABLE(JSON_TO_ARRAY('[1,2]'));
****
+------+-----------+
| x    | table_col |
+------+-----------+
|    1 | 1         |
|    1 | 2         |
|    2 | 1         |
|    2 | 2         |
+------+-----------+

Example 3

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 |
+-----+--------+

Example 4

The TABLE function can be used to expand a field in a table. For example,

CREATE TABLE empRole(Name varchar(20), Role JSON);
INSERT INTO empRole VALUES
  ('Adam', '["Team Lead","Security Engineer"]'),
  ('Mary','["Product Manager"]');
SELECT * FROM empRole;
****
+-------+-----------------------------------+
| Name  | Role                              |
+-------+-----------------------------------+
| Adam  | ["Team Lead","Security Engineer"] |
| Mary  | ["Product Manager"]               |
+------+------------------------------------+

SELECT Name, table_col AS "Title" FROM empRole
JOIN TABLE(JSON_TO_ARRAY(Role));
****
+------+---------------------+
| Name | Title               |
+------+---------------------+
| Adam | "Team Lead"         |
| Adam | "Security Engineer" |
| Mary | "Product Manager"   |
+------+---------------------+

Was this Article Helpful?

Thanks For Your Feedback!