index_object:select()

object index_object¶

index_object:select(search-key, options)¶

Search for a tuple or a set of tuples by the current index. To search by the primary index in the specified space, use the space_object:select() method.

Parameters:	index_object (index_object) – an object reference. key (scalar/table) – a value to be matched against the index key, which may be multi-part. options (table/nil) – none, any, or all of the following parameters: iterator – the iterator type. The default iterator type is ‘EQ’. limit – the maximum number of tuples. offset – the number of tuples to skip (use this parameter carefully when scanning large data sets). options.after – a tuple or the position of a tuple (tuple_pos) after which select starts the search. You can pass an empty string or box.NULL to this option to start the search from the first tuple. options.fetch_pos – if true, the select method returns the position of the last selected tuple as the second value. Note The after and fetch_pos options are supported for the TREE index only.
Return:	This function might return one or two values: The tuples whose fields are equal to the fields of the passed key. If the number of passed fields is less than the number of fields in the current key, then only the passed fields are compared, so select{1,2} matches a tuple whose primary key is {1,2,3}. (Optionally) If options.fetch_pos is set to true, returns a base64-encoded string representing the position of the last selected tuple as the second value. If no tuples are fetched, returns nil.
Rtype:	array of tuples (Optionally) string

Warning

Use the offset option carefully when scanning large data sets as it linearly increases the number of scanned tuples and leads to a full space scan. Instead, you can use the after and fetch_pos options.

Examples:

Below are few examples of using select with different parameters. To try out these examples, you need to bootstrap a Tarantool database as described in Using data operations.

-- Insert test data --
box.space.bands:insert { 1, 'Roxette', 1986 }
box.space.bands:insert { 2, 'Scorpions', 1965 }
box.space.bands:insert { 3, 'Ace of Base', 1987 }
box.space.bands:insert { 4, 'The Beatles', 1960 }
box.space.bands:insert { 5, 'Pink Floyd', 1965 }
box.space.bands:insert { 6, 'The Rolling Stones', 1962 }
box.space.bands:insert { 7, 'The Doors', 1965 }
box.space.bands:insert { 8, 'Nirvana', 1987 }
box.space.bands:insert { 9, 'Led Zeppelin', 1968 }
box.space.bands:insert { 10, 'Queen', 1970 }

-- Select a tuple by the specified primary key value --
select_primary = bands.index.primary:select { 1 }
--[[
---
- - [1, 'Roxette', 1986]
...
--]]

-- Select a tuple by the specified secondary key value --
select_secondary = bands.index.band:select { 'The Doors' }
--[[
---
- - [7, 'The Doors', 1965]
...
--]]

-- Select a tuple by the specified multi-part secondary key value --
select_multipart = bands.index.year_band:select { 1960, 'The Beatles' }
--[[
---
- - [4, 'The Beatles', 1960]
...
--]]

-- Select tuples by the specified partial key value --
select_multipart_partial = bands.index.year_band:select { 1965 }
--[[
---
- - [5, 'Pink Floyd', 1965]
  - [2, 'Scorpions', 1965]
  - [7, 'The Doors', 1965]
...
--]]

-- Select maximum 3 tuples by the specified secondary index --
select_limit = bands.index.band:select({}, { limit = 3 })
--[[
---
- - [3, 'Ace of Base', 1987]
  - [9, 'Led Zeppelin', 1968]
  - [8, 'Nirvana', 1987]
...
--]]

-- Select maximum 3 tuples with the key value greater than 1965 --
select_greater = bands.index.year:select({ 1965 }, { iterator = 'GT', limit = 3 })
--[[
---
- - [9, 'Led Zeppelin', 1968]
  - [10, 'Queen', 1970]
  - [1, 'Roxette', 1986]
...
--]]

-- Select maximum 3 tuples after the specified tuple --
select_after_tuple = bands.index.primary:select({}, { after = { 4, 'The Beatles', 1960 }, limit = 3 })
--[[
---
- - [5, 'Pink Floyd', 1965]
  - [6, 'The Rolling Stones', 1962]
  - [7, 'The Doors', 1965]
...
--]]

-- Select first 3 tuples and fetch a last tuple's position --
result, position = bands.index.primary:select({}, { limit = 3, fetch_pos = true })
-- Then, pass this position as the 'after' parameter --
select_after_position = bands.index.primary:select({}, { limit = 3, after = position })
--[[
---
- - [4, 'The Beatles', 1960]
  - [5, 'Pink Floyd', 1965]
  - [6, 'The Rolling Stones', 1962]
...
--]]

Note

box.space.space-name.index.index-name:select(...)[1]. can be replaced by box.space.space-name.index.index-name:get(...). That is, get can be used as a convenient shorthand to get the first tuple in the tuple set that would be returned by select. However, if there is more than one tuple in the tuple set, then get throws an error.