Is there the way to build index over the field that stores the array in Tarantool?

Question

I need to create a partial index from two parts where the 1st field is an integer and 2nd field is an array.

Then I'll need to select all tuples where the value of the tuple for 1st key is equal and the value of 2nd key is contained in an array.

I.e

box.space.test:select()
[..., 1, [1, 2, 3]]
[..., 1, [3, 4, 5]]
[..., 2, [3, 7, 9]]

box.space.test.index.test:select({1, 3})
[..., 1, [1, 2, 3]]
[..., 1, [3, 4, 5]]

Can it be done using Tarantool?

Answer 1

Yes, this is possible. You can use array index parts in any index. This is described in documentation on the box.space.create_index(). It's not very obvious, and you need to search for "[*]" on the page.

Here's the complete example that you can use:

local yaml = require('yaml')

box.cfg({listen=3301})

box.schema.space.create('test', {if_not_exists=true})
box.space.test:format({
      {name='id',type='unsigned'},
      {name='value',type='unsigned'},
      {name='items',type='array'}})

box.space.test:create_index('primary',
                            {
                               unique = true,
                               parts = { {field = 'id', type = 'unsigned'}},
                               if_not_exists=true})


-- items[*] define a multi-key index part
box.space.test:create_index('secondary',
                            {
                               unique = false,
                               parts = {
                                  {field = 'value', type = 'unsigned'},
                                  {field='items[*]', type='unsigned'}},
                               if_not_exists=true})

box.space.test:put({1, 1, {1,2,3}})
box.space.test:put({2, 1, {2,7,8}})
box.space.test:put({3, 1, {3,7,8}})
box.space.test:put({4, 2, {4,5,6}})

local result = box.space.test.index.secondary:select({1,2})
print(yaml.encode(result))

This code will output the following:

---
- [1, 1, [1, 2, 3]]
- [2, 1, [2, 7, 8]]
...