Basic text search JSON index parameters

You can include JSON index parameters when you create a bts index to control how JSON and BSON columns are indexed.

By default, all field names and values are indexed as unstructured text in the contents field. Use JSON index parameters to control the following aspects of the bts index:

  • Whether to index the documents as field name-value pairs so that you can search for text by field. Enable the all_json_names index parameter to index all field names. Set the json_names index parameters to index specific field names. You have the following choices to further refine how field name-value pairs are indexed:
    • Whether to include JSON or BSON object paths in field name-value pairs so that you can search based on the field hierarchy in the document. Enable the json_path_processing index parameter to index paths.
    • Whether to index the position of values in arrays so that you can search specific positions in arrays. Enable the json_array_processing index parameter to index the position of arrays.
    • Whether to index as both field name-value pairs and unstructured text so that you have the flexibility to search a specific field or all fields. Enable the include_contents index parameter to include an unstructured index of field names and values.
  • Whether an unstructured index contains only values and no field names so that you do not receive field names in search results. Enable the only_json_values index parameter to limit the unstructured index to values.
  • Whether to ignore format errors for JSON or BSON documents. Enable the ignore_json_format_errors index parameter to ignore incorrectly formatted documents.

Requirements and restrictions

The JSON or BSON documents must be in a UTF-8 locale.

Any XML values in a JSON or BSON document are indexed as unstructured text.

The following parts of JSON or BSON documents are indexed by a bts index:

  • JSON string values, or the corresponding BSON element code 0x2.
  • JSON number values, which are converted to string representations: 4-byte integers, 8-byte integers, and 8-byte floating points, or the corresponding BSON element codes: \x01, \x09, \x10, \x11, and \x12.
  • JSON TimeStamp and Coordinated Universal Time Datetime values, which are converted to string representations

The following parts of JSON or BSON documents are not indexed:

  • JSON Boolean true, Boolean false, and null values
  • The BSON element codes: 0x05, 0x06, 0x07, 0x08, 0x0A, 0x0B, 0x0C, 0x0D, 0x0E, 0x0F, 0xFF, and 0x7F.
  • Any name-value pair that has a zero-length field name
  • Fields that contain numbers

You cannot create a composite index on a JSON or BSON column.

Example document

The examples for indexing JSON and BSON documents are based on the following JSON document, which is assumed to be in the docs column of the json_tab table:

{ "person" : {
     "givenname" : "Jim", 
     "surname" : "Flynn",
     "age" : 29,
     "cars" : [ "dodge", "olds" ],
     "parents":[ 
        { "givenname" : "Slim",       
          "surname" : "Flynn" },
        { "givenname" : "Lynn",            
          "surname" : "Kim" }
     ]
}

The bts index on a JSON or BSON document is based on a tree representation of the document. You need to understand the tree representation if you include paths or array positions in the field name-value pairs of a structured index. The example JSON document has the following tree representation:

 "person".
          "givenname" : "Jim"
          "surname” : "Flynn"
          "age" : "29",
          "cars".
                 "0" : "dodge"
                 "1" : "olds"
          "parents".
                    "0".
                        "givenname" : "Slim"
                        "surname" : "Flynn"
                    "1".
                        "givenname" : "Lynn"
                        "surname" : "Kim"