Select from a collection

This section describes how to query columns that are defined on collection types. A collection type is a complex data type in which each collection value contains a group of elements of the same data type. For a detailed description of collection data types, see the HCL OneDB™ Database Design and Implementation Guide. For information about how to access the individual elements that a collection contains, see Handle collections in SELECT statements.

The following figure shows the manager table, which is used in examples throughout this section. The manager table contains both simple and nested collection types. A simple collection is a collection type that does not contain any fields that are themselves collection types. The direct_reports column of the manager table is a simple collection. A nested collection is a collection type that contains another collection type. The projects column of the manager table is a nested collection.

Figure 1: The manager table

CREATE TABLE manager
(
   mgr_name        VARCHAR(30),
   department      VARCHAR(12),
   direct_reports  SET(VARCHAR(30) NOT NULL),
   projects        LIST(ROW(pro_name VARCHAR(15),
                     pro_members SET(VARCHAR(20) NOT NULL) 
                     ) NOT NULL)
)

A query on a column that is a collection type returns, for each row in the table, all the elements that the particular collection contains. For example, the following query shows a query that returns data in the department column and all elements in the direct_reports column for each row of the manager table.

Figure 2: Query

SELECT department, direct_reports FROM manager

Figure 3: Query result

department      marketing
direct_reports  SET {Smith, Waters, Adams, Davis, Kurasawa}

department      engineering
ddirect_reports  SET {Joshi, Davis, Smith, Waters, Fosmire, Evans, Jones}

department      publications
direct_reports  SET {Walker, Fremont, Porat, Johnson}

department      accounting
direct_reports  SET {Baker, Freeman, Jacobs}
;

The output of a query on a collection type always includes the type constructor that specifies whether the collection is a SET, MULTISET, or LIST. For example, in the result, the SET constructor precedes the elements of each collection. Braces ({}) demarcate the elements of a collection; commas separate individual elements of a collection.