How do I select several keys from tarantool at once, like with SELECT IN in SQL?

Question

I want to select several records from Tarantool in one call, but don't see how can I pass several keys to space:get or space:select

Answer 1

You can do it using Lua as well as SQL.

1) Use a stored procedure in Lua, like this one:

function select_several(space_name, index_name, keys)
    local obj = index_name == nil and box.space[space_name] or box.space[space_name].index[index_name]
    local result = {}
    for _, key in pairs(keys) do
        table.insert(result, obj:get(key))
    end
    return result
end
...
select_several('test', nil, {1, 2})

2) Starting with Tarantool 2.0, you can use SQL (provided that you have space format):

box.execute('select * from "test" where "id" in (1, 3);')

Answer 2

One more variant equivalent to the SQL query select * from "test" where "id" in (1, 3) using LuaFun:

tarantool> box.space.test:pairs():filter(function (tuple) return tuple.id == 1 or tuple.id == 3 end):totable()

It is a generic variant if there is no 'id' index in the space, and it implies performing fullscan.

If a unique index named "id" exists, a more efficient variant is possible:

tarantool> fun.iter({1, 3}):map(function (value) return box.space.test.id:get(value) end):totable()

Otherwise if the index is not unique, then it will look like

tarantool> fun.iter({1, 3}):map(function (value) return box.space.test.id:select(value) end):reduce(function (result, data) for _, rec in ipairs(data) do table.insert(result, rec) end return result end, {})