space_object:create_index()

Details about index field types

Index field types differ depending on what values are allowed, and what index types are allowed.

Allowing null for an indexed key

If the index type is TREE, and the index is not the primary index, then the parts={...} clause may include is_nullable=true or is_nullable=false (the default). If is_nullable is true, then it is legal to insert nil or an equivalent such as msgpack.NULL. It is also legal to insert nothing at all when using trailing nullable fields. Within indexes, such «null values» are always treated as equal to other null values, and are always treated as less than non-null values. Nulls may appear multiple times even in a unique index. Example:

box.space.tester:create_index('I',{unique=true,parts={{field = 2, type = 'number', is_nullable = true}}})

Creating an index using field names instead of field numbers

create_index() can use field names and/or field types described by the optional space_object:format() clause. In the following example, we show format() for a space that has two columns named „x“ and „y“, and then we show five variations of the parts={} clause of create_index(), first for the „x“ column, second for both the „x“ and „y“ columns. The variations include omitting the type, using numbers, and adding extra braces.

box.space.tester:format({{name='x', type='scalar'}, {name='y', type='integer'}})
box.space.tester:create_index('I2',{parts={{'x', 'scalar'}}})
box.space.tester:create_index('I3',{parts={{'x','scalar'},{'y','integer'}}})
box.space.tester:create_index('I4',{parts={{1,'scalar'}}})
box.space.tester:create_index('I5',{parts={{1,'scalar'},{2,'integer'}}})
box.space.tester:create_index('I6',{parts={1}})
box.space.tester:create_index('I7',{parts={1,2}})
box.space.tester:create_index('I8',{parts={'x'}})
box.space.tester:create_index('I9',{parts={'x','y'}})
box.space.tester:create_index('I10',{parts={{'x'}}})
box.space.tester:create_index('I11',{parts={{'x'},{'y'}}})

Creating an index using the path option for map fields (JSON-path indexes)

To create an index for a field that is a map (a path string and a scalar value), specify the path string during index_create, that is, parts={ field-number,'data-type',path = 'path-name' }. The index type must be 'tree' or 'hash' and the field’s contents must always be maps with the same path.

Example 1 – The simplest use of path:

-- Result will be - - [{'age': 44}]
box.schema.space.create('T')
box.space.T:create_index('I',{parts={{field = 1, type = 'scalar', path = 'age'}}})
box.space.T:insert{{age=44}}
box.space.T:select(44)

Example 2 – path plus format() plus JSON syntax to add clarity:

-- Result will be: - [1, {'FIO': {'surname': 'Xi', 'firstname': 'Ahmed'}}]
s = box.schema.space.create('T')
format = {{'id', 'unsigned'}, {'data', 'map'}}
s:format(format)
parts = {{'data.FIO["firstname"]', 'str'}, {'data.FIO["surname"]', 'str'}}
i = s:create_index('info', {parts = parts})
s:insert({1, {FIO={firstname='Ahmed', surname='Xi'}}})

Примечание про движок базы данных: vinyl поддерживает только TREE-индексы, и следует создать в vinyl’е вторичные индексы до вставки кортежей.

Creating a multikey index using the path option with [*]

The string in a path option can contain „[*]“ which is called an array index placeholder. Indexes defined with this are useful for JSON documents that all have the same structure.

For example, when creating an index on field#2 for a string document that will start with {'data': [{'name': '...'}, {'name': '...'}], the parts section in the create_index request could look like: parts = {{field = 2, type = 'str', path = 'data[*].name'}}. Then tuples containing names can be retrieved quickly with index_object:select({key-value}).

In fact a single field can have multiple keys, as in this example which retrieves the same tuple twice because there are two keys „A“ and „B“ which both match the request:

s = box.schema.space.create('json_documents')
s:create_index('primarykey')
i = s:create_index('multikey', {parts = {{field = 2, type = 'str', path = 'data[*].name'}}})
s:insert({1,
         {data = {{name='A'},
                  {name='B'}},
          extra_field = 1}})
i:select({''},{iterator='GE'})

The result of the select request looks like this:

tarantool> i:select({''},{iterator='GE'})
---
- - [1, {'data': [{'name': 'A'}, {'name': 'B'}], 'extra_field': 1}]
- [1, {'data': [{'name': 'A'}, {'name': 'B'}], 'extra_field': 1}]
...

Some restrictions exist:

• „[*]“ must be alone or must be at the end of a name in the path
• „[*]“ must not appear twice in the path
• if an index has a path with x[*] then no other index can have a path with x.component
• „[*]“ must not appear in the path of a primary-key
• if an index has unique=true and has a path with „[*]“ then duplicate keys from different tuples are disallowed but duplicate keys for the same tuple are allowed
• as with Using the path option for map fields, the field’s value must have the structure that the path definition implies, or be nil (nil is not indexed)

Creating a functional index

Functional indexes are indexes that call a user-defined function for forming the index key, rather than depending entirely on the Tarantool default formation. Functional indexes are useful for condensing or truncating or reversing or any other way that users want to customize the index.

The function definition must expect a tuple (which has the contents of fields at the time a data-change request happens) and must return a tuple (which has the contents that will actually be put in the index).

The space must have a memtx engine.

The function must be persistent and deterministic.

The key parts must not depend on JSON paths.

The create_index definition must include specification of all key parts, and the function must return a table which has the same number of key parts with the same types.

The function must access key-part values by index, not by field name.

Functional indexes must not be primary-key indexes.

Functional indexes cannot be altered and the function cannot be changed if it is used for an index, so the only way to change them is to drop the index and create it again.

Only sandboxed functions are suitable for functional indexes.

Example:

A function could make a key using only the first letter of a string field.

1. Make a space. The space needs a primary-key field, which is not the field that we will use for the functional index:

   box.schema.space.create('x', {engine = 'memtx'})
   box.space.x:create_index('i',{parts={{field = 1, type = 'string'}}})
   

2. Make a function. The function expects a tuple. In this example it will work on tuple[2] because the key source is field number 2 in what we will insert. Use string.sub() from the string module to get the first character:

   lua_code = [[function(tuple) return {string.sub(tuple[2],1,1)} end]]
   

3. Make the function persistent using the box.schema.func.create function:

   box.schema.func.create('F',
       {body = lua_code, is_deterministic = true, is_sandboxed = true})
   

4. Make a functional index. Specify the fields whose values will be passed to the function. Specify the function:

   box.space.x:create_index('j',{parts={{field = 1, type = 'string'}},func = 'F'})
   

5. Test. Insert a few tuples. Select using only the first letter, it will work because that is the key. Or, select using the same function as was used for insertion:

   box.space.x:insert{'a', 'wombat'}
   box.space.x:insert{'b', 'rabbit'}
   box.space.x.index.j:select('w')
   box.space.x.index.j:select(box.func.F:call({{'x', 'wombat'}}));
   

   The results of the two select requests will look like this:

   tarantool> box.space.x.index.j:select('w')
   ---
   - - ['a', 'wombat']
   ...
   tarantool> box.space.x.index.j:select(box.func.F:call({{'x','wombat'}}));
   ---
   - - ['a', 'wombat']
   ...
   

Here is the full code of the example:

box.schema.space.create('x', {engine = 'memtx'})
box.space.x:create_index('i',{parts={{field = 1, type = 'string'}}})
lua_code = [[function(tuple) return {string.sub(tuple[2],1,1)} end]]
box.schema.func.create('F',
    {body = lua_code, is_deterministic = true, is_sandboxed = true})
box.space.x:create_index('j',{parts={{field = 1, type = 'string'}},func = 'F'})
box.space.x:insert{'a', 'wombat'}
box.space.x:insert{'b', 'rabbit'}
box.space.x.index.j:select('w')
box.space.x.index.j:select(box.func.F:call({{'x', 'wombat'}}));

Functions for functional indexes can return multiple keys. Such functions are called «multikey» functions.

The box.func.create options must include opts = {is_multikey = true}. The return value must be a table of tuples. If a multikey function returns N tuples, then N keys will be added to the index.

Example:

s = box.schema.space.create('withdata')
s:format({{name = 'name', type = 'string'},
          {name = 'address', type = 'string'}})
pk = s:create_index('name', {parts = {{field = 1, type = 'string'}}})
lua_code = [[function(tuple)
               local address = string.split(tuple[2])
               local ret = {}
               for _, v in pairs(address) do
                 table.insert(ret, {utf8.upper(v)})
               end
               return ret
             end]]
box.schema.func.create('address',
                        {body = lua_code,
                         is_deterministic = true,
                         is_sandboxed = true,
                         opts = {is_multikey = true}})
idx = s:create_index('addr', {unique = false,
                              func = 'address',
                              parts = {{field = 1, type = 'string',
                                      collation = 'unicode_ci'}}})
s:insert({"James", "SIS Building Lambeth London UK"})
s:insert({"Sherlock", "221B Baker St Marylebone London NW1 6XE UK"})
idx:select('Uk')
-- Both tuples will be returned.