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

{ BSON_GET | BSON_value_function } ( " BSON_column_name . { BSON_field_name | 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 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;