Basic text search index fields

The bts index indexes searchable data in fields. When you index unstructured text, each value is indexed in a default field called contents. You do not need to specify the default field in the bts_contains() search predicate. When you create an index that has multiple fields because it is a structured or a composite index, you might need to include a field name to modify the search string in the bts_contains() search predicate.

Index fields

{ [ JSONpath . ] | / XMLpath / | [ XMLnamespace \: ] } fieldname

fieldname: The name of the field that is indexed.
JSONpath: If the json_path_processing index parameter is enabled, you can include the path before the field name. Separate each part of the path with a period.
XMLpath: If the xml_path_processing index parameter is enabled, you can include the path before the field name. Separate each part of the path with a forward slash.
XMLnamespace: If the include_namespaces index parameter is enabled, you can include an XML namespace before the field name. Escape the colon in the namespace with a back slash.

If you create a composite index on multiple columns, by default the text from the indexed columns is concatenated into one string and indexed in the contents field. To index the text in each column included in the index under a field of the same name, include the query_default_field="*" index parameter in the index definition. When you query on a composite index that has multiple fields, you must specify the field name in the bts_contains() search predicate.

Searches on structured JSON or XML indexes

When you index structured text by setting XML or JSON index parameters, the names for the XML tags or JSON field names are indexed in separate fields and you must specify those fields in the bts_contains() search predicate.

If you specify a list of XML tags or JSON field names to be indexed with the xmltags or json_names index parameter, the default field is the first field in the field list. You must specify the field name for any other field in the bts_contains() search predicate. However, you can override the default field by setting the query_default_field index parameter to a specific field name to use as the default field.

If you enable the all_xmltags or all_json_names index parameter, there is no default field. You must specify each field name in the bts_contains() search predicate.

Examples: JSON or BSON documents

For these examples, the following JSON document is indexed as field name-value pairs with paths by enabling the all_json_names and json_path_processing index parameters:

{ person : {
     givenname : Jim
}

For example, to search the given name field, you can use either of the following search predicates:

bts_contains(column, ' givenname:Jim ')

bts_contains(column, ' givenname:"Jim" ')

To search for a field that includes a path, include a period between the field name elements. For example, to search the

person:given
name

field, you can use the following search predicate:

bts_contains(column, ' person.givenname:"Jim" ')

Examples: XML documents

For example, if the XML data is indexed in a field that is called fruit, you can use the following search predicates:

bts_contains(column, ' fruit:Orange ')

bts_contains(column, ' fruit:"Orange Juice" ')

If the XML data is indexed in a field that contains the path /fruit/citrus, you can use the following search predicate:

bts_contains(column, ' /fruit/citrus:"Orange Juice" ')

If you enable the include_namespaces index parameter, you must escape the colon (:) in namespaces with a backslash (\). For example, if you are using the fruit namespace:

bts_contains(column, ' fruit\:citrus:Orange ')