Important

The SingleStore 9.1 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 9.1.

TABLE

Syntax

TABLE( [DISTINCT] array) [WITH ORDINALITY]

Arguments

array: A PSQL array of scalar values.

Return Type

A set of table-type rows.

Remarks

When used without the WITH ORDINALITY option, the TABLE() function returns a table with a single column named table_col, where each row contains a single element from the input array.
The WITH ORDINALITY option adds an extra column named table_ord of type BIGINT to the output that contains the 0-based position of each element in the input array. Refer to Example: Use WITH ORDINALITY Option and Example: Use DISTINCT Option.
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-dimensional arrays are not supported as arguments to the TABLE() function.
PSQL arrays are not supported as a table data type or array literals in SingleStore 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.
The TABLE function supports DISTINCT. DISTINCT will de-duplicate the array argument and will generate a table with unique values only.

Examples

Example 1

SQL

SELECT * FROM TABLE([1,2,3]);

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

SQL

SELECT * FROM TABLE(["hello", "world"]);

+-----------+
| table_col |
+-----------+
| hello     |
| world     |
+-----------+

SQL

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,

SQL

CREATE TABLE num (x int);
INSERT INTO num VALUES (1),(2);

SQL

SELECT * FROM num, TABLE([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.

SQL

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.

SQL

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

SQL

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

Example 5: Use DISTINCT Option

To return only distinct values from the input array, eliminating duplicates, use the DISTINCT option as:

SQL

SELECT * FROM TABLE(DISTINCT [1,1,2,3,2]);

+------------+
| table_col  |
+------------+
|   1        |
|   2        |
|   3        |
+------------+

SQL

SELECT * FROM TABLE(DISTINCT['null', 'primary', 'secondary', 'secondary', 'tertiary', 'null', 'secondary', 'tertiary']) WITH ORDINALITY;

+-----------+-----------+
| table_col | table_ord |
+-----------+-----------+
| null      |         0 |
| primary   |         1 |
| secondary |         2 |
| tertiary  |         3 |
+-----------+-----------+

Example 6: Use WITH ORDINALITY Option

The following example shows how to add an ordinality column to the output using the WITH ORDINALITY option. Optionally, you can specify aliases for the table_col and table_ord columns.

SQL

SELECT table_col AS Label, table_ord AS Priority
FROM TABLE(["alpha", "beta", "gamma", "delta"]) WITH ORDINALITY;

+-------+----------+
| Label | Priority |
+-------+----------+
| alpha |        0 |
| beta  |        1 |
| gamma |        2 |
| delta |        3 |
+-------+----------+

You can also use the WITH ORDINALITY option with the TABLE() function in joins. For example, consider the following tasks table:

SQL

CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    label VARCHAR(50) NOT NULL
);

INSERT INTO tasks (task_id, task_name, label) VALUES
(1101, 'Task A', 'gamma'),
(1102, 'Task B', 'alpha'),
(1103, 'Task C', 'beta'),
(1104, 'Task D', 'delta');

Use TABLE() WITH ORDINALITY as follows to assign a priority to each label and then order the tasks by priority:

SQL

WITH labels AS (
    SELECT table_col AS Label, table_ord AS Priority
    FROM TABLE(["alpha", "beta", "gamma", "delta"]) WITH ORDINALITY
)
SELECT t.task_name AS Task, l.Priority
FROM tasks t JOIN labels l
ON t.label = l.Label
ORDER BY l.Priority;

+--------+----------+
| Task   | Priority |
+--------+----------+
| Task B |        0 |
| Task C |        1 |
| Task A |        2 |
| Task D |        3 |
+--------+----------+

Alternatively, you can run this query as:

SQL

SELECT t.task_name AS Task, l.table_ord AS Priority
FROM tasks t JOIN TABLE(["alpha", "beta", "gamma", "delta"]) WITH ORDINALITY AS l
ON t.label = l.table_col ORDER BY l.table_ord;

+--------+----------+
| Task   | Priority |
+--------+----------+
| Task B |        0 |
| Task C |        1 |
| Task A |        2 |
| Task D |        3 |
+--------+----------+