Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE INDEX statementUse the CREATE INDEX statement to create an index for one or more columns in a table, or on values returned by a UDR that uses column values as arguments.
Index-key specificationUse the Index-key specification of the CREATE INDEX statement to define the key value for the index. This can also specify the ascending or descending sort order, and the operator class.
Indexing a BSON fieldYou can create an index on a field within a BSON column.

Indexing a BSON field

You can create an index on a field within a BSON column.

Syntax

Use the following syntax in the index-key specification. See Index-key specification.

BSON field specification


Element	Description	Restrictions	Syntax
`BSON_array_position`	A positive integer that represents the position of a value in an array, starting with 0 for the first value.	Must be preceded by all ancestor field names.
`BSON_column_name`	A BSON column name	Must be of type BSON.	Expression
`BSON_field_name`	BSON field name	Must be a literal BSON field name. Can be a multilevel field identifier, up to 32 levels. All ancestor field names must be included.	Column Expressions
`BSON_value_function`	A BSON value function for a specific data type, except the BSON_VALUE_OBJECTID function	You cannot use the BSON_VALUE_OBJECTID function to create an index	BSON processing functions

You cannot create an index on a BSON column. You must create the index on a field within the BSON column.

The BSON_GET or BSON value function specifies which field to index. The USING BSON keywords are necessary to specify that the index is created on a BSON column.

Example: Create an index on a BSON field

The following statements create and populate a table that has a BSON column:

CREATE DATABASE testdb WITH LOG;
CREATE TABLE IF NOT EXISTS bson_table(bson_col BSON);

INSERT INTO bson_table VALUES(
    '{person:{givenname:"Jim",surname:"Flynn",age:29,cars:["dodge","olds"]}}'
    ::JSON::BSON);

The following statement creates an index on the surname field in the BSON column:

CREATE INDEX idx2 ON bson_table(
   BSON_GET(bson_col, "person.surname")) USING BSON;