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