An index is a nested multikey index if it is created on a field that is present inside an array which in turn is present inside another array.

An index is called a composite multikey index if it is created on more than one field, and at least one of those fields is multikey.

A composite multikey index may have a combination of multikey index paths and simple index paths.

A composite multikey index may have more than one multikey index path. Some of the possibilities of composite multikey indexes are given below.

1. You can use both the .keys() and .values() steps of a map or JSON together.

   The following is an example of a composite multikey index in which both the .keys() and .values() steps of a JSON document are used together. The index is created on the keys and values of the expenses JSON document in the UserInfo table.

   CREATE INDEX multikeyindex4 ON UserInfo (
       info.expenses.keys(),
       info.expenses.values() as ANYATOMIC
   );

2. You can use multiple fields of an array/record/maps-viewed-as-records together. However, the restriction here is that a field cannot be treated as both array and map within a single CREATE INDEX statement.

   The following is an example of a composite multikey index in which multiple fields of an array are used together. The index is created on the passid and issuedby fields in the vpass array in the UserInfo table.

   CREATE INDEX nestedindex1 ON UserInfo (
       info.vehicles[].vpass[].passid AS ANYATOMIC,
       info.vehicles[].vpass[].issuedby AS ANYATOMIC
   );

   Figure 8-2 Composite Multikey Index

   

3. You can use fields of an array/record/maps-viewed-as-records, as well as the fields present inside the inner arrays of those array/record/maps-viewed-as-records together. However, the restriction here is that the immediate parent array of all such fields should be the same.

   The following is an example of a composite multikey index in which fields of an array and fields of an inner array are used together. The index is created on the vid field in the vehicles array and the passid field in the vpass array which is in the vehicles array in the UserInfo table.

   CREATE INDEX nestedindex2 ON UserInfo (
       info.vehicles[].vid AS ANYATOMIC,
       info.vehicles[].vpass[].passid AS ANYATOMIC
   );

   Figure 8-3 Composite Multikey Index

   

The following is an example of a composite multikey index in which fields of an array and fields of an inner array are used together, however, notice that the immediate parent array of the two fields are not the same. The following is an invalid index creation statement. The index is being created on the passid and serviceid fields in the vehicles array in the UserInfo table. Note that within the vehicles array, passid is inside the vpass array, and serviceid is inside the vservice array.

CREATE INDEX invalidindex1 ON UserInfo (
    info.vehicles[].vpass[].passid AS ANYATOMIC,
    info.vehicles[].vservice[].serviceid AS ANYATOMIC
);

Figure 8-4 Composite Multikey Index (invalid)

The following specifications & restrictions apply to multikey index paths:

• You cannot provide predicate or boundary expressions for .keys(), .values(), and [] steps. For more information on predicate and boundary expressions, see the Map-Filter Step Expressions and Array-Slice Step Expressions sections respectively.
• When a multikey index path is evaluated on a table row, it must return zero or more atomic values. If no value is returned, then the special value EMPTY is used.
• No data type declaration is allowed for .keys() step, as the keys() are by default string data type.
• You can provide .keys() steps only as the last step in an index path.
• For Example, info.expenses.keys() is a valid index path whereas info.expenses.keys().books is an invalid index path.
• You can provide .keys() and .values() only after the fields that are maps in strongly typed data.
• You can provide .keys() and .values() steps after a field that you expect to be atomic values or JSON documents inside JSON data.
  • If the fields are atomic values, then the special value EMPTY will be returned.
  • If the fields are json documents, then the corresponding keys or values will be returned.
• You should provide [] steps after those fields that are arrays in strongly typed data.
• You should provide [] steps after those fields that you expect to be arrays inside JSON data.
• If you do not provide [] steps after those fields that you expect to be arrays inside JSON data, then those fields cannot be an array.
  • If the rows in the existing table contain any array inside the JSON data, then the index creation will fail.
  • However, if there are no rows in the existing table that contain an array inside the JSON data, then the index creation will be successful. However, you will not be able to insert a new row with an array in that JSON data in the existing table.
  • For example, in the following badindex1, the [] step is not used with the vehicles array. There can be two scenarios here:

    CREATE INDEX badindex1 ON UserInfo(info.vehicles.vid AS ANYATOMIC);

    • If there are existing rows of data in the UserInfo table where the vehicles field happens to be an array, the index creation will fail.
    • If there are no existing rows of data with the vehicles array in the UserInfo table, the index will be created successfully. However, if you attempt to insert a new row of data where the vehicles attribute is an array, the row insertion will fail.
• You cannot provide [] steps after the non-array fields in strongly typed data.
• You can provide [] steps after the atomic values and JSON documents in JSON data. If you do so, then that atomic value or JSON document will be used.
• If an array contains another array directly, then the corresponding number of []steps are required to index the elements of the inner arrays. For example, if there are two inner arrays inside vehicles, then info.vehicles[][].vid should be used.

Create Index on row-property functions

In a CREATE INDEX statement, you must provide these functions without any argument. The row you are indexing is implicitly considered as the input to the function.

Example:

CREATE INDEX idx_modtime ON users(modification_time())

SELECT * FROM Users $u WHERE 
modification_time($u) > "2022-08-01T10:45:00"

This query returns all the rows whose most recent modification time is after 2022-08-01T10:45:00. It uses the idx_modtime index defined above. You can verify this by viewing the query plan using the show query command.

More examples of creating indexes on functions

Download the script acctstream_loaddata.sql and run it as shown below. This script creates the stream_acct table used in the example below and loads data into the table.

java -jar lib/kvstore.jar kvlite -secure-config disable
 java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

load -file acctstream_loaddata.sql

CREATE INDEX idx_country ON stream_acct(substring(acct_data.country as string, 0,3))

Example 2: Using a multi-key index path

CREATE INDEX idx_showid_year_month ON 
stream_acct(acct_data.contentStreamed[].showId AS INTEGER,
substring(acct_data.contentStreamed[].seriesInfo[].episodes[].date AS STRING,0, 4),
substring(acct_data.contentStreamed[].seriesInfo[].episodes[].date AS STRING,5, 2))

SELECT count(*) FROM stream_acct s1 WHERE EXISTS 
s1.acct_data.contentStreamed[$element.showId = 16].seriesInfo.
episodes[substring($element.date, 0, 4) = "2022"]

show query SELECT count(*) FROM stream_acct s1 WHERE EXISTS
> s1.acct_data.contentStreamed[$element.showId = 16].seriesInfo.episodes[substring($element.date, 0, 4) = "2022"]

{
  "iterator kind" : "GROUP",
  "input variable" : "$gb-1",
  "input iterator" :
  {
    "iterator kind" : "RECEIVE",
    "distribution kind" : "ALL_SHARDS",
    "distinct by fields at positions" : [ 1 ],
    "input iterator" :
    {
      "iterator kind" : "SELECT",
      "FROM" :
      {
        "iterator kind" : "TABLE",
        "target table" : "stream_acct",
        "row variable" : "$$s1",
        "index used" : "idx_showid_year_month",
        "covering index" : true,
        "index row variable" : "$$s1_idx",
        "index scans" : [
          {
            "equality conditions" : {"acct_data.contentStreamed[].showId":16,"substring#acct_data.contentStreamed[].seriesInfo[].episodes[].date@,0,4":"2022"},
            "range conditions" : {}
          }
        ]
      },
      "FROM variable" : "$$s1_idx",
      "SELECT expressions" : [
        {
          "field name" : "Column_1",
          "field expression" :
          {
            "iterator kind" : "CONST",
            "value" : 1
          }
        },
        {
          "field name" : "acct_id_gen",
          "field expression" :
          {
            "iterator kind" : "FIELD_STEP",
            "field name" : "#acct_id",
            "input iterator" :
            {
              "iterator kind" : "VAR_REF",
              "variable" : "$$s1_idx"
            }
          }
        }
      ]
    }
  },
  "grouping expressions" : [

  ],
  "aggregate functions" : [
    {
      "iterator kind" : "FUNC_COUNT_STAR"
    }
  ]
}