Data creation and modification

You can add, update, replace and delete your indexed data using different ways provided by Manticore. Manticore supports working with external storages such as databases, XML, CSV and TSV documents. For insert and delete operations, transaction mechanism is supported.

Also, for insert and replace queries, Manticore supports the Elasticsearch-like query format along with its own format. For details, see the corresponding examples in the Adding documents to a real-time table and REPLACE sections.

▪️ Adding documents to a table

Adding documents to a real-time table

If you are looking for information about adding documents to a plain table please read section about adding data from external storages.

Adding documents in a real-time manner is only supported for Real-Time and percolate tables. Corresponding SQL command or HTTP endpoint or a client’s functions inserts new rows (documents) into a table with provided field values. Note that it is not necessary for a table to already exist before adding documents to it. If the table does not exist, Manticore will attempt to create it automatically. For more information, see Auto schema.

You can insert a single or multiple documents with values for all fields of the table or only part of them. In this case the other fields will be filled with their default values (0 for scalar types, empty string for text types).

Expressions are currently not supported in INSERT and the values should be explicitly specified.

The ID field/value can be omitted as RT and PQ tables support auto-id functionality. You can also use 0 as the id value to force automatic ID generation. Rows with duplicate IDs will not be overwritten by INSERT. You can use REPLACE for that.

When using the HTTP JSON protocol, two different request formats are available: a common Manticore format and an Elasticsearch-like one. Both formats are demonstrated in the examples.

Also, if you use JSON and the Manticore request format, note that the doc node is mandatory and all the values should be provided inside it.

  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Javascript
  • Java

SQL JSON Elasticsearch PHP Python Javascript Java

General syntax:

  1. INSERT INTO <table name> [(column, ...)]
  2. VALUES (value, ...)
  3. [, (...)]
  1. INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
  2. INSERT INTO products(title) VALUES ('Crossbody Bag with Tassel');
  3. INSERT INTO products VALUES (0,'Yellow bag', 4.95);
  1. POST /insert
  2. {
  3.   "index":"products",
  4.   "id":1,
  5.   "doc":
  6.   {
  7.     "title" : "Crossbody Bag with Tassel",
  8.     "price" : 19.85
  9.   }
  10. }
  12. POST /insert
  13. {
  14.   "index":"products",
  15.   "id":2,
  16.   "doc":
  17.   {
  18.     "title" : "Crossbody Bag with Tassel"
  19.   }
  20. }
  22. POST /insert
  23. {
  24.   "index":"products",
  25.   "id":0,
  26.   "doc":
  27.   {
  28.     "title" : "Yellow bag"
  29.   }
  30. }
  1. POST /products/_create/3
  2. {
  3.   "title": "Yellow Bag with Tassel",
  4.   "price": 19.85
  5. }
  7. POST /products/_create/
  8. {
  9.   "title": "Red Bag with Tassel",
  10.   "price": 19.85
  11. }
  1. $index->addDocuments([
  2.         ['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85]
  3. ]);
  4. $index->addDocuments([
  5.         ['id' => 2, 'title' => 'Crossbody Bag with Tassel']
  6. ]);
  7. $index->addDocuments([
  8.         ['id' => 0, 'title' => 'Yellow bag']
  9. ]);
  1. indexApi.insert({"index" : "test", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}})
  2. indexApi.insert({"index" : "test", "id" : 2, "doc" : {"title" : "Crossbody Bag with Tassel"}})
  3. indexApi.insert({"index" : "test", "id" : 0, "doc" : {{"title" : "Yellow bag"}})
  1. res = await indexApi.insert({"index" : "test", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}});
  2. res = await indexApi.insert({"index" : "test", "id" : 2, "doc" : {"title" : "Crossbody Bag with Tassel"}});
  3. res = await indexApi.insert({"index" : "test", "id" : 0, "doc" : {{"title" : "Yellow bag"}});
  1. InsertDocumentRequest newdoc = new InsertDocumentRequest();
  2. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  3.     put("title","Crossbody Bag with Tassel");
  4.     put("price",19.85);
  5. }};
  6. newdoc.index("products").id(1L).setDoc(doc);
  7. sqlresult = indexApi.insert(newdoc);
  9. newdoc = new InsertDocumentRequest();
  10. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  11.     put("title","Crossbody Bag with Tassel");
  12. }};
  13. newdoc.index("products").id(2L).setDoc(doc);
  14. sqlresult = indexApi.insert(newdoc);
  16. newdoc = new InsertDocumentRequest();
  17. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  18.     put("title","Yellow bag");
  19.  }};
  20. newdoc.index("products").id(0L).setDoc(doc);
  21. sqlresult = indexApi.insert(newdoc);

Response

  1. Query OK, 1 rows affected (0.00 sec)
  2. Query OK, 1 rows affected (0.00 sec)
  3. Query OK, 1 rows affected (0.00 sec)
  1. {
  2.   "_index": "products",
  3.   "_id": 1,
  4.   "created": true,
  5.   "result": "created",
  6.   "status": 201
  7. }
  8. {
  9.   "_index": "products",
  10.   "_id": 2,
  11.   "created": true,
  12.   "result": "created",
  13.   "status": 201
  14. }
  15. {
  16.   "_index": "products",
  17.   "_id": 0,
  18.   "created": true,
  19.   "result": "created",
  20.   "status": 201
  21. }
  1. {
  2. "_id":3,
  3. "_index":"products",
  4. "_primary_term":1,
  5. "_seq_no":0,
  6. "_shards":{
  7.     "failed":0,
  8.     "successful":1,
  9.     "total":1
  10. },
  11. "_type":"_doc",
  12. "_version":1,
  13. "result":"updated"
  14. }
  15. {
  16. "_id":2235747273424240642,
  17. "_index":"products",
  18. "_primary_term":1,
  19. "_seq_no":0,
  20. "_shards":{
  21.     "failed":0,
  22.     "successful":1,
  23.     "total":1
  24. },
  25. "_type":"_doc",
  26. "_version":1,
  27. "result":"updated"
  28. }

Auto schema

Manticore has a mechanism for automatically creating tables when a specified table in the INSERT statement does not yet exist. This mechanism is enabled by default. To disable it, set auto_schema = 0 in the Searchd section of your Manticore config file.

By default, all text values in the VALUES clause are considered to be of the text type, with the exception of values that represent valid email addresses, which are treated as the string type.

If you try to INSERT multiple rows with different, incompatible value types for the same field, auto table creation will be canceled and an error message will be returned. However, if the different value types are compatible, the resulting field type will be the one that accommodates all the values. Some automatic data type conversions that may occur include:

  • mva -> mva64
  • uint -> bigint -> float
  • string -> text
  • SQL
  • JSON

SQL JSON

  1. MySQL [(none)]> drop table if exists t; insert into t(i,f,t,s,j,b,m,mb) values(123,1.2,'text here','test@mail.com','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777)); desc t; select * from t;
  1. POST /insert  -d
  2. {
  3.  "index":"t",
  4.  "id": 2,
  5.  "doc":
  6.  {
  7.    "i" : 123,
  8.    "f" : 1.23,
  9.    "t": "text here",
  10.    "s": "test@mail.com",
  11.    "j": {"a": 123},
  12.    "b": 1099511627776,
  13.    "m": [1,2],
  14.    "mb": [1099511627776,1099511627777]
  15.  }
  16. }

Response

  1. --------------
  2. drop table if exists t
  3. --------------
  5. Query OK, 0 rows affected (0.42 sec)
  7. --------------
  8. insert into t(i,f,t,j,b,m,mb) values(123,1.2,'text here','{"a": 123}',1099511627776,(1,2),(1099511627776,1099511627777))
  9. --------------
  11. Query OK, 1 row affected (0.00 sec)
  13. --------------
  14. desc t
  15. --------------
  17. +-------+--------+----------------+
  18. | Field | Type   | Properties     |
  19. +-------+--------+----------------+
  20. | id    | bigint |                |
  21. | t     | text   | indexed stored |
  22. | s     | string |                |
  23. | j     | json   |                |
  24. | i     | uint   |                |
  25. | b     | bigint |                |
  26. | f     | float  |                |
  27. | m     | mva    |                |
  28. | mb    | mva64  |                |
  29. +-------+--------+----------------+
  30. 8 rows in set (0.00 sec)
  32. --------------
  33. select * from t
  34. --------------
  36. +---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
  37. | id                  | i    | b             | f        | m    | mb                          | t         | s             | j          |
  38. +---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
  39. | 5045949922868723723 |  123 | 1099511627776 | 1.200000 | 1,2  | 1099511627776,1099511627777 | text here | test@mail.com | {"a": 123} |
  40. +---------------------+------+---------------+----------+------+-----------------------------+-----------+---------------+------------+
  41. 1 row in set (0.00 sec)
  1. {"_index":"t","_id":2,"created":true,"result":"created","status":201}

Auto ID

There is an auto ID generation functionality for column ID of documents inserted or replaced into an real-time or a Percolate table. The generator produces a unique ID of a document with some guarantees and should not be considered an auto-incremented ID.

The value of ID generated is guaranteed to be unique under the following conditions:

  • server_id value of the current server is in range of 0 to 127 and is unique among nodes in the cluster or it uses the default value generated from MAC address as a seed
  • system time does not change for the Manticore node between server restarts
  • auto ID is generated fewer than 16 million times per second between search server restarts

The auto ID generator creates 64 bit integer for a document ID and uses the following schema:

  • 0 to 23 bits is a counter that gets incremented on every call to auto ID generator
  • 24 to 55 bits is a unix timestamp of the server start
  • 56 to 63 bits is a server_id

This schema allows to be sure that the generated ID is unique among all nodes at the cluster and that data inserted into different cluster nodes does not create collisions between the nodes.

That is why the first ID from the generator used for auto ID is NOT 1 but a larger number. Also documents stream inserted into a table might have not sequential ID values if inserts into other tables happen between the calls as the ID generator is single in the server and shared between all its tables.

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

SQL JSON PHP Python Javascript Java

  1. INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85);
  2. INSERT INTO products VALUES (0,'Yello bag', 4.95);
  3. select * from products;
  1. POST /insert
  2. {
  3.   "index":"products",
  4.   "id":0,
  5.   "doc":
  6.   {
  7.     "title" : "Yellow bag"
  8.   }
  9. }
  11. GET /search
  12. {
  13.   "index":"products",
  14.   "query":{
  15.     "query_string":""
  16.   }
  17. }
  1. $index->addDocuments([
  2.         ['id' => 0, 'title' => 'Yellow bag']
  3. ]);
  1. indexApi.insert({"index" : "products", "id" : 0, "doc" : {"title" : "Yellow bag"}})
  1. res = await indexApi.insert({"index" : "products", "id" : 0, "doc" : {"title" : "Yellow bag"}});
  1. newdoc = new InsertDocumentRequest();
  2. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  3.     put("title","Yellow bag");
  4.  }};
  5. newdoc.index("products").id(0L).setDoc(doc);
  6. sqlresult = indexApi.insert(newdoc);

Response

  1. +---------------------+-----------+---------------------------+
  2. | id                  | price     | title                     |
  3. +---------------------+-----------+---------------------------+
  4. | 1657860156022587404 | 19.850000 | Crossbody Bag with Tassel |
  5. | 1657860156022587405 |  4.950000 | Yello bag                 |
  6. +---------------------+-----------+---------------------------+
  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 1,
  6.     "hits": [
  7.       {
  8.         "_id": "1657860156022587406",
  9.         "_score": 1,
  10.         "_source": {
  11.           "price": 0,
  12.           "title": "Yellow bag"
  13.         }
  14.       }
  15.     ]
  16.   }
  17. }

Bulk adding documents

You can insert into a real-time table not just a single document, but as many as you want. It’s ok to insert into a real-time table in batches of tens of thousands of documents. What’s important to know in this case:

  • the larger the batch the higher is the latency of each insert operation
  • the larger the batch the higher indexation speed you can expect
  • each batch insert operation is considered a single transaction with atomicity guarantee, so you will either have all the new documents in the table at once or in case of a failure none of them will be added
  • you might want to increase max_packet_size value to allow bigger batches
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • Javascript
  • Java

SQL JSON Elasticsearch PHP Python Javascript Java

For bulk insert just provide more documents in brackets after VALUES(). The syntax is:

  1. INSERT INTO <table name>[(column1, column2, ...)] VALUES ()[,(value1,[value2, ...])]

Optional column name list lets you explicitly specify values for some of the columns present in the table. All the other columns will be filled with their default values (0 for scalar types, empty string for string types).

For example:

  1. INSERT INTO products(title,price) VALUES ('Crossbody Bag with Tassel', 19.85), ('microfiber sheet set', 19.99), ('Pet Hair Remover Glove', 7.99);

The syntax is in general the same as for inserting a single document, just provide more lines one for each document and use /bulk endpoint instead of /insert and enclose each document into node “insert”. Note that it also requires:

  • Content-Type: application/x-ndjson
  • The data itself should be formatted as a newline-delimited json (NDJSON). Basically it means that each line should contain exactly one json statement and end with a newline \n and maybe \r.

Notice, bulk endpoint supports ‘insert’, ‘replace’, ‘delete’, and ‘update’ queries. Also notice, that you can direct operations to several different tables, however transactions are possible only over single table, so if you specify more, manticore will collect operations directed to one table into single txn, and when table changes, it will commit collected and start new transaction over new table.

  1. POST /bulk
  2. -H "Content-Type: application/x-ndjson" -d '
  3. {"insert": {"index":"products", "id":1, "doc":  {"title":"Crossbody Bag with Tassel","price" : 19.85}}}
  4. {"insert":{"index":"products", "id":2, "doc":  {"title":"microfiber sheet set","price" : 19.99}}}
  5. '
  7. POST /bulk
  8. -H "Content-Type: application/x-ndjson" -d '
  9. {"insert":{"index":"test1","id":21,"doc":{"int_col":1,"price":1.1,"title":"bulk doc one"}}}
  10. {"insert":{"index":"test1","id":22,"doc":{"int_col":2,"price":2.2,"title":"bulk doc two"}}}
  11. {"insert":{"index":"test1","id":23,"doc":{"int_col":3,"price":3.3,"title":"bulk doc three"}}}
  12. {"insert":{"index":"test2","id":24,"doc":{"int_col":4,"price":4.4,"title":"bulk doc four"}}}
  13. {"insert":{"index":"test2","id":25,"doc":{"int_col":5,"price":5.5,"title":"bulk doc five"}}}
  14. '
  1. POST /_bulk
  2. -H "Content-Type: application/x-ndjson" -d '
  3. { "index" : { "_index" : "products" } }
  4. { "title" : "Yellow Bag", "price": 12 }
  5. { "create" : { "_index" : "products" } }
  6. { "title" : "Red Bag", "price": 12.5, "id": 3 }
  7. '

Use method addDocuments():

  1. $index->addDocuments([
  2.         ['id' => 1, 'title' => 'Crossbody Bag with Tassel', 'price' => 19.85],
  3.         ['id' => 2, 'title' => 'microfiber sheet set', 'price' => 19.99],
  4.         ['id' => 3, 'title' => 'Pet Hair Remover Glove', 'price' => 7.99]
  5. ]);
  1. docs = [ \
  2.     {"insert": {"index" : "products", "id" : 1, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}}, \
  3.     {"insert": {"index" : "products", "id" : 2, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}}, \
  4.     {"insert": {"index" : "products", "id" : 3, "doc" : {"title" : "CPet Hair Remover Glove", "price" : 7.99}}}
  5. ]
  6. res = indexApi.bulk('\n'.join(map(json.dumps,docs)))
  1. let docs = [
  2.     {"insert": {"index" : "products", "id" : 3, "doc" : {"title" : "Crossbody Bag with Tassel", "price" : 19.85}}},
  3.     {"insert": {"index" : "products", "id" : 4, "doc" : {"title" : "microfiber sheet set", "price" : 19.99}}},
  4.     {"insert": {"index" : "products", "id" : 5, "doc" : {"title" : "CPet Hair Remover Glove", "price" : 7.99}}}
  5. ];
  6. res =  await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));
  1. String body = "{\"insert\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"Crossbody Bag with Tassel\", \"price\" : 19.85}}}"+"\n"+
  2.     "{\"insert\": {\"index\" : \"products\", \"id\" : 4, \"doc\" : {\"title\" : \"microfiber sheet set\", \"price\" : 19.99}}}"+"\n"+
  3.     "{\"insert\": {\"index\" : \"products\", \"id\" : 5, \"doc\" : {\"title\" : \"CPet Hair Remover Glove\", \"price\" : 7.99}}}"+"\n";         
  4. BulkResponse bulkresult = indexApi.bulk(body);

Response

  1. Query OK, 3 rows affected (0.01 sec)

Expressions are not currently supported in INSERT and values should be explicitly specified.

  1. {
  2.   "items": [
  3.     {
  4.       "bulk": {
  5.         "_index": "products",
  6.         "_id": 2,
  7.         "created": 2,
  8.         "deleted": 2,
  9.         "updated": 0,
  10.         "result": "created",
  11.         "status": 201
  12.       }
  13.     }
  14.   ],
  15.   "errors": false
  16. }
  18. {
  19.   "items": [
  20.     {
  21.       "bulk": {
  22.         "_index": "test1",
  23.         "_id": 23,
  24.         "created": 3,
  25.         "deleted": 0,
  26.         "updated": 0,
  27.         "result": "created",
  28.         "status": 201
  29.       }
  30.     },
  31.     {
  32.       "bulk": {
  33.         "_index": "test2",
  34.         "_id": 25,
  35.         "created": 2,
  36.         "deleted": 0,
  37.         "updated": 0,
  38.         "result": "created",
  39.         "status": 201
  40.       }
  41.     }
  42.   ],
  43.   "errors": false
  44. }
  1. {
  2. "items":[
  3.     {
  4.     "_id":"0",
  5.     "_index":"products",
  6.     "_primary_term":1,
  7.     "_seq_no":0,
  8.     "_shards":{
  9.         "failed":0,
  10.         "successful":1,
  11.         "total":1
  12.     },
  13.     "_type":"_doc",
  14.     "_version":1,
  15.     "result":"created",
  16.     "status":201
  17.     },
  18.    {
  19.     "_id":"0",
  20.     "_index":"products",
  21.     "_primary_term":1,
  22.     "_seq_no":0,
  23.     "_shards":{
  24.         "failed":0,
  25.         "successful":1,
  26.         "total":1
  27.     },
  28.     "_type":"_doc",
  29.     "_version":1,
  30.     "result":"created",
  31.     "status":201
  32.     }    
  33. ],
  34. "errors":false,
  35. "took":1
  36. }

Inserting multi-value attributes (MVA) values

Multi-value attributes (MVA) are inserted as arrays of numbers.

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

SQL JSON PHP Python Javascript Java

  1. INSERT INTO products(title, sizes) VALUES('shoes', (40,41,42,43));
  2. POST /insert
  3. {
  4.   "index":"products",
  5.   "id":1,
  6.   "doc":
  7.   {
  8.     "title" : "shoes",
  9.     "sizes" : [40, 41, 42, 43]
  10.   }
  11. }
  1. $index->addDocument(
  2.   ['title' => 'shoes', 'sizes' => [40,41,42,43]],
  3.   1
  4. );
  1. indexApi.insert({"index" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","sizes":[40,41,42,43]}})
  1. res = await indexApi.insert({"index" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","sizes":[40,41,42,43]}});
  1. newdoc = new InsertDocumentRequest();
  2. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  3.     put("title","Yellow bag");
  4.     put("sizes",new int[]{40,41,42,43});
  5.  }};
  6. newdoc.index("products").id(0L).setDoc(doc);
  7. sqlresult = indexApi.insert(newdoc);

Inserting JSON

JSON value can be inserted as as an escaped string (via SQL, HTTP, PHP) or as a JSON object (via HTTP).

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

SQL JSON PHP Python Javascript Java

  1. INSERT INTO products VALUES (1, 'shoes', '{"size": 41, "color": "red"}');

JSON value can be inserted as as JSON object

  1. POST /insert
  2. {
  3.   "index":"products",
  4.   "id":1,
  5.   "doc":
  6.   {
  7.     "title" : "shoes",
  8.     "meta" : {
  9.       "size": 41,
  10.       "color": "red"
  11.     }
  12.   }
  13. }

JSON value can be also inserted as a string containing escaped JSON:

  1. POST /insert
  2. {
  3.   "index":"products",
  4.   "id":1,
  5.   "doc":
  6.   {
  7.     "title" : "shoes",
  8.     "meta" : "{\"size\": 41, \"color\": \"red\"}"
  9.   }
  10. }
  1. $index->addDocument(
  2.   ['title' => 'shoes', 'meta' => '{"size": 41, "color": "red"}'],
  3.   1
  4. );
  1. indexApi = api = manticoresearch.IndexApi(client)
  2. indexApi.insert({"index" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","meta":'{"size": 41, "color": "red"}'}})
  2. res = await indexApi.insert({"index" : "products", "id" : 0, "doc" : {"title" : "Yellow bag","meta":'{"size": 41, "color": "red"}'}});
  1. newdoc = new InsertDocumentRequest();
  2. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  3.     put("title","Yellow bag");
  4.     put("meta",
  5.         new HashMap<String,Object>(){{
  6.             put("size",41);
  7.             put("color","red");
  8.         }});
  9.  }};
  10. newdoc.index("products").id(0L).setDoc(doc);
  11. sqlresult = indexApi.insert(newdoc);