Indexing a BSON field

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


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

Read syntax diagramSkip visual syntax diagram
BSON field specification

                                                       V                         |         
   '-BSON_value_function-'                               '-BSON_array_position-'           

>--USING BSON---------------------------------------------------|

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 TABLE IF NOT EXISTS bson_table(bson_col 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;