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:

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"

Copyright© 2018 HCL Technologies Limited