Data types

Full-text fields and attributes

Manticore’s data types can be split into full-text fields and attributes.

Full-text fields

Full-text fields:

  • can be indexed with natural language processing algorithms, therefore can be searched for keywords
  • cannot be used for sorting or grouping
  • original document’s content can be retrieved
  • original document’s content can be used for highlighting

Full-text fields are represented data type text. All the other data types are called “attributes”.

Attributes

Attributes are non-full-text values associated with each document that can be used to perform non-full-text filtering, sorting and grouping during search.

It is often desired to process full-text search results based not only on matching document ID and its rank, but on a number of other per-document values as well. For instance, one might need to sort news search results by date and then relevance, or search through products within specified price range, or limit blog search to posts made by selected users, or group results by month. To do that efficiently, Manticore enables not only full-text fields, but additional attributes to each document. It’s then possible to use them to filter, sort, or group full-text matches or search only by attributes.

The attributes, unlike full-text fields, are not full-text indexed. They are stored in the table, but it is not possible to search them as full-text.

A good example for attributes would be a forum posts table. Assume that only title and content fields need to be full-text searchable - but that sometimes it is also required to limit search to a certain author or a sub-forum (ie. search only those rows that have some specific values of author_id or forum_id); or to sort matches by post_date column; or to group matching posts by month of the post_date and calculate per-group match counts.

  • SQL
  • JSON
  • PHP
  • Python
  • Javascript
  • Java
  • config

SQL JSON PHP Python Javascript Java config

  1. CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp);
  1. POST /cli -d "CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('forum');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'content'=>['type'=>'text'],
  6.     'author_id'=>['type'=>'int'],
  7.     'forum_id'=>['type'=>'int'],
  8.     'post_date'=>['type'=>'timestamp']
  9. ]);
  1. utilsApi.sql('CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)')
  1. res = await utilsApi.sql('CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)');
  1. utilsApi.sql("CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp)");
  1. table forum
  2. {
  3.     type = rt
  4.     path = forum
  6.     # when configuring fields via config, they are indexed (and not stored) by default
  7.     rt_field = title
  8.     rt_field = content
  10.     # this option needs to be specified for the field to be stored
  11.     stored_fields = title, content
  13.     rt_attr_uint = author_id
  14.     rt_attr_uint = forum_id
  15.     rt_attr_timestamp = post_date
  16. }

This example shows running a full-text query filtered by author_id, forum_id and sorted by post_date.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select * from forum where author_id=123 and forum_id in (1,3,7) order by post_date desc
  1. POST /search
  2. {
  3.   "index": "forum",
  4.   "query":
  5.   {
  6.     "match_all": {},
  7.     "bool":
  8.     {
  9.       "must":
  10.       [
  11.         { "equals": { "author_id": 123 } },
  12.         { "in": { "forum_id": [1,3,7] } }
  13.       ]
  14.     }
  15.   },
  16.   "sort": [ { "post_date": "desc" } ]
  17. }
  1. $client->search([
  2.         'index' => 'forum',
  3.         'query' =>
  4.         [
  5.             'match_all' => [],
  6.             'bool' => [
  7.                 'must' => [
  8.                     'equals' => ['author_id' => 123],
  9.                     'in' => [
  10.                         'forum_id' => [
  11.                             1,3,7
  12.                         ]
  13.                     ]
  14.                 ]
  15.             ]
  16.         ],
  17.         'sort' => [
  18.         ['post_date' => 'desc']
  19.     ]
  20. ]);
  1. searchApi.search({"index":"forum","query":{"match_all":{},"bool":{"must":[{"equals":{"author_id":123}},{"in":{"forum_id":[1,3,7]}}]}},"sort":[{"post_date":"desc"}]})
  1. res = await searchApi.search({"index":"forum","query":{"match_all":{},"bool":{"must":[{"equals":{"author_id":123}},{"in":{"forum_id":[1,3,7]}}]}},"sort":[{"post_date":"desc"}]});
  1. HashMap<String,Object> filters = new HashMap<String,Object>(){{
  2.     put("must", new HashMap<String,Object>(){{
  3.         put("equals",new HashMap<String,Integer>(){{
  4.             put("author_id",123);
  5.         }});
  6.         put("in",
  7.             new HashMap<String,Object>(){{
  8.                 put("forum_id",new int[] {1,3,7});
  9.         }});
  10.     }});
  11. }};
  12. Map<String,Object> query = new HashMap<String,Object>();
  13. query.put("match_all",null);
  14. query.put("bool",filters);
  15. SearchRequest searchRequest = new SearchRequest();
  16. searchRequest.setIndex("forum");
  17. searchRequest.setQuery(query);
  18. searchRequest.setSort(new ArrayList<Object>(){{
  19.     add(new HashMap<String,String>(){{ put("post_date","desc");}});
  20. }});
  21. SearchResponse searchResponse = searchApi.search(searchRequest);

Row-wise and columnar attribute storages

Manticore supports two types of attribute storages:

As can be understood from their names, they store data differently. The traditional row-wise storage:

  • stores attributes uncompressed
  • all attributes of the same document are stored in one row close to each other
  • rows are stored one by one
  • accessing attributes is basically done by just multiplying rowid by stride (length of a single vector) and getting the requested attribute from the calculated memory location. It gives very low random access latency
  • attributes have to be in memory to get acceptable performance, otherwise due to the row-wise nature of the storage Manticore may have to read from disk too much unneded data which is in many cases suboptimal.

With the columnar storage:

  • each attribute is stored independently from all other attributes in its separate “column”
  • storage is split into blocks of 65536 entries
  • the blocks are stored compressed. This often allows to store just a few distinct values instead of storing all of them like in the row-wise storage. High compression ratio allows to read from disk faster and makes the memory requirement much lower
  • when data is indexed, storage scheme is selected for each block independently. For example, if all values in a block are the same, it gets “const” storage and only one value is stored for the whole block. If there are less than 256 unique values per block, it gets “table” storage and stores indexes to a table of values instead of the values themselves
  • search in a block can be early rejected if it’s clear the requested value is not present in the block.

The columnar storage was designed to handle large data volume that does not fit into RAM, so the recommendations are:

  • if you have enough memory for all your attributes you will benefit from the row-wise storage
  • otherwise the columnar storage can still give you decent performance with much lower memory footprint which will allow you to store much more documents in your table

How to switch between the storages

The traditional row-wise storage is default, so if you want everything to be stored in a row-wise fashion you don’t need to do anything when you create a table.

To enable the columnar storage you need to:

  • specify engine='columnar' in CREATE TABLE to make all attributes of the table columnar. Then if you want to keep a specific attribute row-wise you need to add engine='rowwise' when you declare it. For example:

    1. create table tbl(title text, type int, price float engine='rowwise') engine='columnar'

  • specify engine='columnar' for a specific attribute in CREATE TABLE to make it columnar. For example:

    1. create table tbl(title text, type int, price float engine='columnar');

    or

    1. create table tbl(title text, type int, price float engine='columnar') engine='rowwise';

  • in the plain mode you need to list attributes you want to be columnar in columnar_attrs.

Below is the list of data types supported by Manticore Search:

Document ID

The document identifier is a mandatory attribute. It must be a unique, signed 64-bit integer. While the document ID can be specified explicitly, it is still enabled even if not specified. Document IDs cannot be UPDATE‘ed.

  • Explicit ID
  • Implicit ID

Explicit ID Implicit ID

When you create a table you can specify ID explicitly, but no matter what datatype you use it will be always as said previously - signed 64-bit integer.

  1. CREATE TABLE tbl(id bigint, content text);
  2. DESC tbl;

You can also omit specifying ID at all, it will be enabled automatically.

  1. CREATE TABLE tbl(content text);
  2. DESC tbl;

Response

  1. +---------+--------+----------------+
  2. | Field   | Type   | Properties     |
  3. +---------+--------+----------------+
  4. | id      | bigint |                |
  5. | content | text   | indexed stored |
  6. +---------+--------+----------------+
  7. 2 rows in set (0.00 sec)
  1. +---------+--------+----------------+
  2. | Field   | Type   | Properties     |
  3. +---------+--------+----------------+
  4. | id      | bigint |                |
  5. | content | text   | indexed stored |
  6. +---------+--------+----------------+
  7. 2 rows in set (0.00 sec)

Character data types

General syntax:

  1. string|text [stored|attribute] [indexed]

Properties:

  1. indexed - full-text indexed (can be used in full-text queries)
  2. stored - stored in a docstore (stored on disk, not in RAM, lazy read)
  3. attribute - makes it string attribute (can sort/group by it)

Specifying at least one property overrides all the default ones (see below), i.e. if you decide to use a custom combination of properties you need to list all the properties you want.

No properties specified:

string and text are aliases, but if you don’t specify any properties they by default means different things:

  • just string by default means attribute (see details below).
  • just text by default means stored + indexed (see details below).

Text

Text (just text or text/string indexed) data type forms the full-text part of the table. Text fields are indexed and can be searched for keywords.

Text is passed through an analyzer pipeline that converts the text to words, applies morphology transformations etc. Eventually a full-text table (a special data structure that enables quick searches for a keyword) gets built from that text.

Full-text fields can only be used in MATCH() clause and cannot be used for sorting or aggregation. Words are stored in an inverted index along with references to the fields they belong and positions in the field. This allows to search a word inside each field and to use advanced operators like proximity. By default the original text of the fields is both indexed and stored in document storage. It means that the original text can be returned with the query results and it can be used in search result highlighting.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text);
  1. POST /cli -d "CREATE TABLE products(title text)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text']
  5. ]);
  1. utilsApi.sql('CREATE TABLE products(title text)')
  1. res = await utilsApi.sql('CREATE TABLE products(title text)');
  1. utilsApi.sql("CREATE TABLE products(title text)");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     # when configuring fields via config, they are indexed (and not stored) by default
  7.     rt_field = title
  9.     # this option needs to be specified for the field to be stored
  10.     stored_fields = title
  11. }

This behavior can be overridden by explicitly specifying that the text is only indexed.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text indexed);
  1. POST /cli -d "CREATE TABLE products(title text indexed)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text','options'=>['indexed']]
  5. ]);
  1. utilsApi.sql('CREATE TABLE products(title text indexed)')
  1. res = await utilsApi.sql('CREATE TABLE products(title text indexed)');
  1. utilsApi.sql("CREATE TABLE products(title text indexed)");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     # when configuring fields via config, they are indexed (and not stored) by default
  7.     rt_field = title
  8. }

Fields are named, and you can limit your searches to a single field (e.g. search through “title” only) or a subset of fields (eg. to “title” and “abstract” only). Manticore table format generally supports up to 256 full-text fields.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select * from products where match('@title first');
  1. POST /search
  2. {
  3.     "index": "products",
  4.     "query":
  5.     {
  6.         "match": { "title": "first" }
  7.     }
  8. }
  1. $index->setName('products')->search('@title')->get();
  1. searchApi.search({"index":"products","query":{"match":{"title":"first"}}})
  1. res = await searchApi.search({"index":"products","query":{"match":{"title":"first"}}});
  1. utilsApi.sql("CREATE TABLE products(title text indexed)");

String

Unlike full-text fields, string attributes (just string or string/text attribute) are stored as they are received and cannot be used in full-text searches. Instead they are returned in results, they can be used in WHERE clause for comparison filtering or REGEX and they can be used for sorting and aggregation. In general it’s not recommended to store large texts in string attributes, but use string attributes for metadata like names, titles, tags, keys.

If want to also index the string attribute, can specify both as string attribute indexed. Will allow full-text searching, and works as an attribute.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, keys string);
  1. POST /cli -d "CREATE TABLE products(title text, keys string)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'keys'=>['type'=>'string']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, keys string)')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, keys string)');
  1. utilsApi.sql("CREATE TABLE products(title text, keys string)");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_string = keys
  10. }

MORE

Creating a local table - 图1

Integer

Integer type allows storing 32 bit unsigned integer values.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, price int);
  1. POST /cli -d "CREATE TABLE products(title text, price int)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'price'=>['type'=>'int']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, price int)')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, price int)');
  1. utilsApi.sql("CREATE TABLE products(title text, price int)");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_uint = type
  10. }

Integers can be stored in shorter sizes than 32 bit by specifying a bit count. For example if we want to store a numeric value which we know is not going to be bigger than 8, the type can be defined as bit(3). Bitcount integers perform slower than the full size ones, but they require less RAM. They are saved in 32-bit chunks, so in order to save space they should be grouped at the end of attributes definitions (otherwise a bitcount integer between 2 full-size integers will occupy 32 bits as well).

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, flags bit(3), tags bit(2) );
  1. POST /cli -d "CREATE TABLE products(title text, flags bit(3), tags bit(2))"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'flags'=>['type'=>'bit(3)'],
  6.     'tags'=>['type'=>'bit(2)']
  7. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, flags bit(3), tags bit(2) ')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, flags bit(3), tags bit(2) ');
  1. utilsApi.sql("CREATE TABLE products(title text, flags bit(3), tags bit(2)");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_uint = flags:3
  10.     rt_attr_uint = tags:2
  11. }

Big Integer

Big integers are 64-bit wide signed integers.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, price bigint );
  1. POST /cli -d "CREATE TABLE products(title text, price bigint)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'price'=>['type'=>'bigint']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, price bigint )')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, price bigint )');
  1. utilsApi.sql("CREATE TABLE products(title text, price bigint )");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_bigint = type
  10. }

Boolean

Declares a boolean attribute. It’s equivalent to an integer attribute with bit count of 1.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, sold bool );
  1. POST /cli -d "CREATE TABLE products(title text, sold bool)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'sold'=>['type'=>'bool']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, sold bool )')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, sold bool )');
  1. utilsApi.sql("CREATE TABLE products(title text, sold bool )");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_bool = sold
  10. }

Timestamps

Timestamp type represents unix timestamps which is stored as a 32-bit integer. The difference is that time and date functions are available for the timestamp type.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, date timestamp);
  1. POST /cli -d "CREATE TABLE products(title text, date timestamp)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'date'=>['type'=>'timestamp']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, date timestamp)')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, date timestamp)');
  1. utilsApi.sql("CREATE TABLE products(title text, date timestamp)");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_timestamp = date
  10. }

Float

Real numbers are stored as 32-bit IEEE 754 single precision floats.

  • SQL
  • JSON
  • PHP
  • Python
  • java
  • javascript
  • config

SQL JSON PHP Python java javascript config

  1. CREATE TABLE products(title text, coeff float);
  1. POST /cli -d "CREATE TABLE products(title text, coeff float)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'coeff'=>['type'=>'float']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, coeff float)')
  1. utilsApi.sql("CREATE TABLE products(title text, coeff float)");
  1. res = await utilsApi.sql('CREATE TABLE products(title text, coeff float)');
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_float = coeff
  10. }

Unlike integer types, equal comparison of floats is forbidden due to rounding errors. A near equal can be used instead, by checking the absolute error margin.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select abs(a-b)<=0.00001 from products
  1. POST /search
  2. {
  3.   "index": "products",
  4.   "query": { "match_all": {} } },
  5.   "expressions": { "eps": "abs(a-b)" }
  6. }
  1. $index->setName('products')->search('')->expression('eps','abs(a-b)')->get();
  1. searchApi.search({"index":"products","query":{"match_all":{}},"expressions":{"eps":"abs(a-b)"}})
  1. res = await searchApi.search({"index":"products","query":{"match_all":{}}},"expressions":{"eps":"abs(a-b)"}});
  1. searchRequest = new SearchRequest();
  2. searchRequest.setIndex("forum");
  3. query = new HashMap<String,Object>();
  4. query.put("match_all",null);
  5. searchRequest.setQuery(query);
  6. Object expressions = new HashMap<String,Object>(){{
  7.     put("ebs","abs(a-b)");
  8. }};
  9. searchRequest.setExpressions(expressions);
  10. searchResponse = searchApi.search(searchRequest);

Another alternative, which can also be used to perform IN(attr,val1,val2,val3) is to compare floats as integers by choosing a multiplier factor and convert the floats to integers in operations. Example illustrates modifying IN(attr,2.0,2.5,3.5) to work with integer values.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select in(ceil(attr*100),200,250,350) from products
  1. POST /search
  2. {
  3.   "index": "products",
  4.   "query": { "match_all": {} } },
  5.   "expressions": { "inc": "in(ceil(attr*100),200,250,350)" }
  6. }
  1. $index->setName('products')->search('')->expression('inc','in(ceil(attr*100),200,250,350)')->get();
  1. searchApi.search({"index":"products","query":{"match_all":{}}},"expressions":{"inc":"in(ceil(attr*100),200,250,350)"}})
  1. res = await searchApi.search({"index":"products","query":{"match_all":{}}},"expressions":{"inc":"in(ceil(attr*100),200,250,350)"}});
  1. searchRequest = new SearchRequest();
  2. searchRequest.setIndex("forum");
  3. query = new HashMap<String,Object>();
  4. query.put("match_all",null);
  5. searchRequest.setQuery(query);
  6. Object expressions = new HashMap<String,Object>(){{
  7.     put("inc","in(ceil(attr*100),200,250,350)");
  8. }};
  9. searchRequest.setExpressions(expressions);
  10. searchResponse = searchApi.search(searchRequest);

JSON

This data type allows storing JSON objects for schema-less data. It is not supported by the columnar storage, but since you can combine the both storages in the same table you can have it stored in the traditional storage instead.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, data json);
  1. POST /cli -d "CREATE TABLE products(title text, data json)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'data'=>['type'=>'json']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, data json)')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, data json)');
  1. utilsApi.sql'CREATE TABLE products(title text, data json)');
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_json = data
  10. }

JSON properties can be used in most operations. There are also special functions such as ALL()), ANY()), GREATEST()), LEAST()) and INDEXOF()) that allow traversal of property arrays.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select indexof(x>2 for x in data.intarray) from products
  1. POST /search
  2. {
  3.   "index": "products",
  4.   "query": { "match_all": {} } },
  5.   "expressions": { "idx": "indexof(x>2 for x in data.intarray)" }
  6. }
  1. $index->setName('products')->search('')->expression('idx','indexof(x>2 for x in data.intarray)')->get();
  1. searchApi.search({"index":"products","query":{"match_all":{}}},"expressions":{"idx":"indexof(x>2 for x in data.intarray)"}})
  1. res = await searchApi.search({"index":"products","query":{"match_all":{}}},"expressions":{"idx":"indexof(x>2 for x in data.intarray)"}});
  1. searchRequest = new SearchRequest();
  2. searchRequest.setIndex("forum");
  3. query = new HashMap<String,Object>();
  4. query.put("match_all",null);
  5. searchRequest.setQuery(query);
  6. Object expressions = new HashMap<String,Object>(){{
  7.     put("idx","indexof(x>2 for x in data.intarray)");
  8. }};
  9. searchRequest.setExpressions(expressions);
  10. searchResponse = searchApi.search(searchRequest);

Text properties are treated same as strings so it’s not possible to use them in full-text matches expressions, but string functions like REGEX()) can be used.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select regex(data.name, 'est') as c from products where c>0
  1. POST /search
  2. {
  3.   "index": "products",
  4.   "query":
  5.   {
  6.     "match_all": {},
  7.     "range": { "c": { "gt": 0 } } }
  8.   },
  9.   "expressions": { "c": "regex(data.name, 'est')" }
  10. }
  1. $index->setName('products')->search('')->expression('idx',"regex(data.name, 'est')")->filter('c','gt',0)->get();
  1. searchApi.search({"index":"products","query":{"match_all":{},"range":{"c":{"gt":0}}}},"expressions":{"c":"regex(data.name, 'est')"}})
  1. res = await searchApi.search({"index":"products","query":{"match_all":{},"range":{"c":{"gt":0}}}},"expressions":{"c":"regex(data.name, 'est')"}});
  1. searchRequest = new SearchRequest();
  2. searchRequest.setIndex("forum");
  3. query = new HashMap<String,Object>();
  4. query.put("match_all",null);
  5. query.put("range", new HashMap<String,Object>(){{
  6.     put("c", new HashMap<String,Object>(){{
  7.         put("gt",0);
  8.     }});
  9. }});
  10. searchRequest.setQuery(query);
  11. Object expressions = new HashMap<String,Object>(){{
  12.     put("idx","indexof(x>2 for x in data.intarray)");
  13. }};
  14. searchRequest.setExpressions(expressions);
  15. searchResponse = searchApi.search(searchRequest);

In case of JSON properties, enforcing data type is required to be casted in some situations for proper functionality. For example in case of float values DOUBLE()) must be used for proper sorting.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select * from products order by double(data.myfloat) desc
  1. POST /search
  2. {
  3.   "index": "products",
  4.   "query": { "match_all": {} } },
  5.   "sort": [ { "double(data.myfloat)": { "order": "desc"} } ]
  6. }
  1. $index->setName('products')->search('')->sort('double(data.myfloat)','desc')->get();
  1. searchApi.search({"index":"products","query":{"match_all":{}}},"sort":[{"double(data.myfloat)":{"order":"desc"}}]})
  1. res = await searchApi.search({"index":"products","query":{"match_all":{}}},"sort":[{"double(data.myfloat)":{"order":"desc"}}]});
  1. searchRequest = new SearchRequest();
  2. searchRequest.setIndex("forum");
  3. query = new HashMap<String,Object>();
  4. query.put("match_all",null);
  5. searchRequest.setQuery(query);
  6. searchRequest.setSort(new ArrayList<Object>(){{
  7.     add(new HashMap<String,String>(){{ put("double(data.myfloat)",new HashMap<String,String>(){{ put("order","desc");}});}});
  8. }});
  9. searchResponse = searchApi.search(searchRequest);

Multi-value integer (MVA)

Multi-value attributes allow storing variable-length lists of 32-bit unsigned integers. It can be used to store one-to-many numeric values like tags, product categories, properties.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, product_codes multi);
  1. POST /cli -d "CREATE TABLE products(title text, product_codes multi)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'product_codes'=>['type'=>'multi']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, product_codes multi)')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, product_codes multi)');
  1. utilsApi.sql("CREATE TABLE products(title text, product_codes multi)");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_multi = product_codes
  10. }

It supports filtering and aggregation, but not sorting. Filtering can be made of a condition that requires at least one element to pass (using ANY())) or all (ALL())).

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select * from products where any(product_codes)=3
  1. POST /search
  2. {
  3.   "index": "products",
  4.   "query":
  5.   {
  6.     "match_all": {},
  7.     "equals" : { "any(product_codes)": 3 }
  8.   }
  9. }
  1. $index->setName('products')->search('')->filter('any(product_codes)','equals',3)->get();
  1. searchApi.search({"index":"products","query":{"match_all":{},"equals":{"any(product_codes)":3}}}})
  1. res = await searchApi.search({"index":"products","query":{"match_all":{},"equals":{"any(product_codes)":3}}}})'
  1. searchRequest = new SearchRequest();
  2. searchRequest.setIndex("forum");
  3. query = new HashMap<String,Object>();
  4. query.put("match_all",null);
  5. query.put("equals",new HashMap<String,Integer>(){{
  6.      put("any(product_codes)",3);
  7. }});
  8. searchRequest.setQuery(query);
  9. searchRequest.setExpressions(expressions);
  10. searchResponse = searchApi.search(searchRequest);

Information like least) or greatest) element and length of the list can be extracted. An example shows ordering by the least element of a multi-value attribute.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. select least(product_codes) l from products order by l asc
  1. POST /search
  2. {
  3.   "index": "products",
  4.   "query":
  5.   {
  6.     "match_all": {},
  7.     "sort": [ { "product_codes":{ "order":"asc", "mode":"min" } } ]
  8.   }
  9. }
  1. $index->setName('products')->search('')->sort('product_codes','asc','min')->get();
  1. searchApi.search({"index":"products","query":{"match_all":{},"sort":[{"product_codes":{"order":"asc","mode":"min"}}]}})
  1. res = await searchApi.search({"index":"products","query":{"match_all":{},"sort":[{"product_codes":{"order":"asc","mode":"min"}}]}});
  1. searchRequest = new SearchRequest();
  2. searchRequest.setIndex("forum");
  3. query = new HashMap<String,Object>();
  4. query.put("match_all",null);
  5. searchRequest.setQuery(query);
  6. searchRequest.setSort(new ArrayList<Object>(){{
  7.     add(new HashMap<String,String>(){{ put("product_codes",new HashMap<String,String>(){{ put("order","asc");put("mode","min");}});}});
  8. }});
  9. searchResponse = searchApi.search(searchRequest);

When grouping by multi-value attribute, a document will contribute to as many groups as there are different values associated with that document. For instance, if the collection contains exactly 1 document having a ‘product_codes’ multi-value attribute with values 5, 7, and 11, grouping on ‘product_codes’ will produce 3 groups with COUNT(*)equal to 1 and GROUPBY() key values of 5, 7, and 11 respectively. Also note that grouping by multi-value attributes might lead to duplicate documents in the result set because each document can participate in many groups.

  • SQL

SQL

  1. insert into products values ( 1, 'doc one', (5,7,11) );
  2. select id, count(*), groupby() from products group by product_codes;

Response

  1. Query OK, 1 row affected (0.00 sec)
  3. +------+----------+-----------+
  4. | id   | count(*) | groupby() |
  5. +------+----------+-----------+
  6. |    1 |        1 |        11 |
  7. |    1 |        1 |         7 |
  8. |    1 |        1 |         5 |
  9. +------+----------+-----------+
  10. 3 rows in set (0.00 sec)

The order of the numbers inserted as values of multi-valued attributes is not preserved. Values are stored internally as a sorted set.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java

SQL JSON PHP Python javascript java

  1. insert into product values (1,'first',(4,2,1,3));
  2. select * from products;
  1. POST /insert
  2. {
  3.     "index":"products",
  4.     "id":1,
  5.     "doc":
  6.     {
  7.         "title":"first",
  8.         "product_codes":[4,2,1,3]
  9.     }
  10. }
  12. POST /search
  13. {
  14.   "index": "products",
  15.   "query": { "match_all": {} }
  16. }
  1. $index->addDocument([
  2.     "title"=>"first",
  3.     "product_codes"=>[4,2,1,3]
  4. ]);
  5. $index->search('')-get();
  1. indexApi.insert({"index":"products","id":1,"doc":{"title":"first","product_codes":[4,2,1,3]}})
  2. searchApi.search({"index":"products","query":{"match_all":{}}})
  1. await indexApi.insert({"index":"products","id":1,"doc":{"title":"first","product_codes":[4,2,1,3]}});
  2. res = await searchApi.search({"index":"products","query":{"match_all":{}}});
  1. InsertDocumentRequest newdoc = new InsertDocumentRequest();
  2. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  3.     put("title","first");
  4.     put("product_codes",new int[] {4,2,1,3});
  5. }};
  6. newdoc.index("products").id(1L).setDoc(doc);
  7. sqlresult = indexApi.insert(newdoc);
  8. Map<String,Object> query = new HashMap<String,Object>();
  9. query.put("match_all",null);
  10. SearchRequest searchRequest = new SearchRequest();
  11. searchRequest.setIndex("products");
  12. searchRequest.setQuery(query);
  13. SearchResponse searchResponse = searchApi.search(searchRequest);
  14. System.out.println(searchResponse.toString() );

Response

  1. Query OK, 1 row affected (0.00 sec)
  3. +------+---------------+-------+
  4. | id   | product_codes | title |
  5. +------+---------------+-------+
  6. |    1 | 1,2,3,4       | first |
  7. +------+---------------+-------+
  8. 1 row in set (0.01 sec)
  1. {
  2.    "_index":"products",
  3.    "_id":1,
  4.    "created":true,
  5.    "result":"created",
  6.    "status":201
  7. }
  9. {
  10.    "took":0,
  11.    "timed_out":false,
  12.    "hits":{
  13.       "total":1,
  14.       "hits":[
  15.          {
  16.             "_id":"1",
  17.             "_score":1,
  18.             "_source":{
  19.                "product_codes":[
  20.                   1,
  21.                   2,
  22.                   3,
  23.                   4
  24.                ],
  25.                "title":"first"
  26.             }
  27.          }
  28.       ]
  29.    }
  30. }
  1. Array
  2. (
  3.     [_index] => products
  4.     [_id] => 1
  5.     [created] => 1
  6.     [result] => created
  7.     [status] => 201
  8. )
  9. Array
  10. (
  11.     [took] => 0
  12.     [timed_out] =>
  13.     [hits] => Array
  14.         (
  15.             [total] => 1
  16.             [hits] => Array
  17.                 (
  18.                     [0] => Array
  19.                         (
  20.                             [_id] => 1
  21.                             [_score] => 1
  22.                             [_source] => Array
  23.                                 (
  24.                                     [product_codes] => Array
  25.                                         (
  26.                                             [0] => 1
  27.                                             [1] => 2
  28.                                             [2] => 3
  29.                                             [3] => 4
  30.                                         )
  32.                                     [title] => first
  33.                                 )
  34.                         )
  35.                 )
  36.         )
  37. )
  1. {'created': True,
  2.  'found': None,
  3.  'id': 1,
  4.  'index': 'products',
  5.  'result': 'created'}
  6. {'hits': {'hits': [{u'_id': u'1',
  7.                     u'_score': 1,
  8.                     u'_source': {u'product_codes': [1, 2, 3, 4],
  9.                                  u'title': u'first'}}],
  10.           'total': 1},
  11.  'profile': None,
  12.  'timed_out': False,
  13.  'took': 29}
  1. {"took":0,"timed_out":false,"hits":{"total":1,"hits":[{"_id":"1","_score":1,"_source":{"product_codes":[1,2,3,4],"title":"first"}}]}}
  1. class SearchResponse {
  2.     took: 0
  3.     timedOut: false
  4.     hits: class SearchResponseHits {
  5.         total: 1
  6.         hits: [{_id=1, _score=1, _source={product_codes=[1, 2, 3, 4], title=first}}]
  7.         aggregations: null
  8.     }
  9.     profile: null
  10. }

Multi-value big integer

A data type type that allows storing variable-length lists of 64-bit signed integers. It has the same functionality as multi-value integer.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • java
  • config

SQL JSON PHP Python javascript java config

  1. CREATE TABLE products(title text, values multi64);
  1. POST /cli -d "CREATE TABLE products(title text, values multi64)"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'values'=>['type'=>'multi64']
  6. ]);
  1. utilsApi.sql('CREATE TABLE products(title text, values multi64))')
  1. res = await utilsApi.sql('CREATE TABLE products(title text, values multi64))');
  1. utilsApi.sql("CREATE TABLE products(title text, values multi64))");
  1. table products
  2. {
  3.     type = rt
  4.     path = products
  6.     rt_field = title
  7.     stored_fields = title
  9.     rt_attr_multi_64 = values
  10. }

Columnar attribute properties

When you use the columnar storage you can specify the following properties for the attributes.

fast_fetch

By default Manticore Columnar storage stores all attributes not only in columnar fashion, but in a special docstore row by row which enables fast execution of queries like SELECT * FROM ... especially when you are fetching lots of records at once. But if you are sure you don’t need it or want to save disk space you can disable it by specifying fast_fetch='0' when you create a table or (if you are defining a table in a config) use columnar_no_fast_fetch as shown in the following example.

  • RT mode
  • Plain mode

RT mode Plain mode

  1. create table t(a int, b int fast_fetch='0') engine='columnar'; desc t;
  1. source min {
  2.     type = mysql
  3.     sql_host = localhost
  4.     sql_user = test
  5.     sql_pass =
  6.     sql_db = test
  7.     sql_query = select 1, 1 a, 1 b
  8.     sql_attr_uint = a
  9.     sql_attr_uint = b
  10. }
  12. table tbl {
  13.     path = tbl/col
  14.     source = min
  15.     columnar_attrs = *
  16.     columnar_no_fast_fetch = b
  17. }

Response

  1. +-------+--------+---------------------+
  2. | Field | Type   | Properties          |
  3. +-------+--------+---------------------+
  4. | id    | bigint | columnar fast_fetch |
  5. | a     | uint   | columnar fast_fetch |
  6. | b     | uint   | columnar            |
  7. +-------+--------+---------------------+
  8. 3 rows in set (0.00 sec)
  1. +-------+--------+---------------------+
  2. | Field | Type   | Properties          |
  3. +-------+--------+---------------------+
  4. | id    | bigint | columnar fast_fetch |
  5. | a     | uint   | columnar fast_fetch |
  6. | b     | uint   | columnar            |
  7. +-------+--------+---------------------+

Creating a local table

There are 2 different approaches to deal with tables in Manticore:

Online schema management (RT mode)

Real-time mode requires no table definition in the configuration file, but presence of data_dir directive in searchd section is mandatory. Index files are stored inside the data_dir.

Replication is available only in this mode.

In this mode you can use SQL commands like CREATE TABLE, ALTER TABLE and DROP TABLE to create and change table schema and drop it. This mode is especially useful for real-time and percolate tables.

Table names are case insensitive in the RT mode.

Defining table schema in config (Plain mode)

In this mode you can specify table schema in config which will be read on Manticore start and if the table doesn’t exist yet it will be created. This mode is especially useful for plain tables that are built upon indexing data from an external storage.

Dropping tables is only possible by removing them from the configuration file or by removing the path setting and sending a HUP signal to the server or restarting it.

Table names are case sensitive in this mode.

All table types are supported in this mode.

Table types and modes

Index typeRT modePlain mode
Real-timesupportedsupported
Plainnot supportedsupported
Percolatesupportedsupported
Distributedsupportedsupported
Templatenot supportedsupported

Real-time table

Real-time table is the main type of tables in Manticore. It allows adding, updating and deleting documents with immediate availability of the changes. Real-time table settings can be defined in a configuration file or online via CREATE/UPDATE/DELETE/ALTER commands.

Real-time table internally consists of one or multiple plain tables called chunks. There can be:

  • multiple disk chunks. They are stored on disk with the same structure as any plain table
  • single ram chunk. Stored in memory and used as an accumulator of changes

RAM chunk size is controlled by rt_mem_limit. Once the limit is exceeded the RAM chunk is flushed to disk in a form of a disk chunk. When there are too many disk chunks they can be merged into one for better performance using command OPTIMIZE.

  • SQL
  • JSON
  • PHP
  • Python
  • Javascript
  • Java
  • CONFIG

SQL JSON PHP Python Javascript Java CONFIG

  1. CREATE TABLE products(title text, price float) morphology='stem_en';
  1. POST /cli -d "CREATE TABLE products(title text, price float)  morphology='stem_en'"
  1. $index = new \Manticoresearch\Index($client);
  2. $index->setName('products');
  3. $index->create([
  4.     'title'=>['type'=>'text'],
  5.     'price'=>['type'=>'float'],
  6. ]);
  1. utilsApi.sql('CREATE TABLE forum(title text, price float)')
  1. res = await utilsApi.sql('CREATE TABLE forum(title text, price float)');
  1. utilsApi.sql("CREATE TABLE forum(title text, price float)");
  1. table products {
  2.   type = rt
  3.   path = tbl
  4.   rt_field = title
  5.   rt_attr_uint = price
  6.   stored_fields = title
  7. }

Response

  1. Query OK, 0 rows affected (0.00 sec)
  1. {
  2. "total":0,
  3. "error":"",
  4. "warning":""
  5. }
Creating a real-time table via JSON over HTTP:

👍 What you can do with a real-time table:

⛔ What you cannot do with a real-time table:

  • Index data with help of indexer
  • Link it with sources for easy indexing from external storages
  • Update it’s killlist_target, it’s just not needed as the real-time table takes controls of it automatically

Real-time table files structure

ExtensionDescription
.locklock file
.ramRAM chunk
.metaRT table headers
..spdisk chunks (see plain table format)