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

user_7772459 0
Viewed 138

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?

tarantool indexing arrays
1 Answers

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]]
...
user_136265360
Related