REPLACE

REPLACE works similar to INSERT, but it marks the old document with the same ID as a new document as deleted before inserting a new document.

When using the HTTP JSON protocol, two different request formats are available: a Manticore format and an Elasticsearch-like format. Both formats can be seen in the provided examples.

  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • javascript
  • Java

SQL JSON Elasticsearch PHP Python javascript Java

  1. REPLACE INTO products VALUES(1, "document one", 10);
  1. POST /replace
  2. -H "Content-Type: application/x-ndjson" -d '
  3. {
  4.   "index":"products",
  5.   "id":1,
  6.   "doc":
  7.   {
  8.     "title":"product one",
  9.     "price":10
  10.   }
  11. }
  12. '
  1. PUT /products/_doc/2
  2. {
  3.   "title": "product two",
  4.   "price": 20
  5. }
  7. POST /products/_doc/
  8. {
  9.   "title": "product three",
  10.   "price": 10
  11. }
  1. $index->replaceDocument([
  2.    'title' => 'document one',
  3.     'price' => 10
  4. ],1);
  1. indexApi.replace({"index" : "products", "id" : 1, "doc" : {"title" : "document one","price":10}})
  1. res = await indexApi.replace({"index" : "products", "id" : 1, "doc" : {"title" : "document one","price":10}});
  1. docRequest = new InsertDocumentRequest();
  2. HashMap<String,Object> doc = new HashMap<String,Object>(){{
  3.             put("title","document one");
  4.             put("price",10);
  5. }};
  6. docRequest.index("products").id(1L).setDoc(doc);
  7. sqlresult = indexApi.replace(docRequest);

Response

  1. Query OK, 1 row affected (0.00 sec)
  1. {
  2.   "_index":"products",
  3.   "_id":1,
  4.   "created":false,
  5.   "result":"updated",
  6.   "status":200
  7. }
  1. {
  2. "_id":2,
  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":"created"
  14. }
  16. {
  17. "_id":2235747273424240642,
  18. "_index":"products",
  19. "_primary_term":1,
  20. "_seq_no":0,
  21. "_shards":{
  22.     "failed":0,
  23.     "successful":1,
  24.     "total":1
  25. },
  26. "_type":"_doc",
  27. "_version":1,
  28. "result":"created"
  29. }
  1. Array(
  2.     [_index] => products
  3.     [_id] => 1
  4.     [created] => false
  5.     [result] => updated
  6.     [status] => 200
  7. )
  1. {'created': False,
  2.  'found': None,
  3.  'id': 1,
  4.  'index': 'products',
  5.  'result': 'updated'}
  1. {"_index":"products","_id":1,"result":"updated"}
  1. class SuccessResponse {
  2.     index: products
  3.     id: 1
  4.     created: false
  5.     result: updated
  6.     found: null
  7. }

REPLACE is supported for RT and PQ tables.

The old document is not removed from the table, it is only marked as deleted. Because of this the table size grows until table chunks are merged and documents marked as deleted in these chunks are not included in the chunk created as a result of merge. You can force chunk merge by using OPTIMIZE statement.

The syntax of the REPLACE statement is identical to INSERT syntax:

  1. REPLACE INTO table [(column1, column2, ...)]
  2.     VALUES (value1, value2, ...)
  3.     [, (...)]

REPLACE using HTTP protocol is performed via the /replace endpoint. There’s also a synonym endpoint, /index.

Multiple documents can be replaced at once. See bulk adding documents for more details.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. REPLACE INTO products(id,title,tag) VALUES (1, 'doc one', 10), (2,' doc two', 20);
  1. POST /bulk
  2. -H "Content-Type: application/x-ndjson" -d '
  3. { "replace" : { "index" : "products", "id":1, "doc": { "title": "doc one", "tag" : 10 } } }
  4. { "replace" : { "index" : "products", "id":2, "doc": { "title": "doc two", "tag" : 20 } } }
  5. '
  1. $index->replaceDocuments([
  2.     [   
  3.         'id' => 1,
  4.         'title' => 'document one',
  5.         'tag' => 10
  6.     ],
  7.     [   
  8.         'id' => 2,
  9.         'title' => 'document one',
  10.         'tag' => 20
  11.     ]
  12. );
  1. indexApi = manticoresearch.IndexApi(client)
  2. docs = [ \
  3.     {"replace": {"index" : "products", "id" : 1, "doc" : {"title" : "document one"}}}, \
  4.     {"replace": {"index" : "products", "id" : 2, "doc" : {"title" : "document two"}}} ]
  5. api_resp = indexApi.bulk('\n'.join(map(json.dumps,docs)))
  1. docs = [
  2.     {"replace": {"index" : "products", "id" : 1, "doc" : {"title" : "document one"}}},
  3.     {"replace": {"index" : "products", "id" : 2, "doc" : {"title" : "document two"}}} ];
  4. res =  await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));
  1. body = "{\"replace\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"document one\"}}}" +"\n"+
  2.     "{\"replace\": {\"index\" : \"products\", \"id\" : 2, \"doc\" : {\"title\" : \"document two\"}}}"+"\n" ;         
  3. indexApi.bulk(body);

Response

  1. Query OK, 2 rows affected (0.00 sec)
  1. {
  2.   "items":
  3.   [
  4.     {
  5.       "replace":
  6.       {
  7.         "_index":"products",
  8.         "_id":1,
  9.         "created":false,
  10.         "result":"updated",
  11.         "status":200
  12.       }
  13.     },
  14.     {
  15.       "replace":
  16.       {
  17.         "_index":"products",
  18.         "_id":2,
  19.         "created":false,
  20.         "result":"updated",
  21.         "status":200
  22.       }
  23.     }
  24.   ],
  25.   "errors":false
  26. }
  1. Array(
  2.     [items] =>
  3.     Array(
  4.         Array(
  5.             [_index] => products
  6.             [_id] => 2
  7.             [created] => false
  8.             [result] => updated
  9.             [status] => 200
  10.         )
  11.         Array(
  12.             [_index] => products
  13.             [_id] => 2
  14.             [created] => false
  15.             [result] => updated
  16.             [status] => 200
  17.         )
  18.     )
  19.     [errors => false
  20. )
  1. {'error': None,
  2.  'items': [{u'replace': {u'_id': 1,
  3.                          u'_index': u'products',
  4.                          u'created': False,
  5.                          u'result': u'updated',
  6.                          u'status': 200}},
  7.            {u'replace': {u'_id': 2,
  8.                          u'_index': u'products',
  9.                          u'created': False,
  10.                          u'result': u'updated',
  11.                          u'status': 200}}]}
  1. {"items":[{"replace":{"_index":"products","_id":1,"created":false,"result":"updated","status":200}},{"replace":{"_index":"products","_id":2,"created":false,"result":"updated","status":200}}],"errors":false}
  1. class BulkResponse {
  2.     items: [{replace={_index=products, _id=1, created=false, result=updated, status=200}}, {replace={_index=products, _id=2, created=false, result=updated, status=200}}]
  3.     error: null
  4.     additionalProperties: {errors=false}
  5. }

UPDATE

UPDATE changes row-wise attribute values of existing documents in a specified table with new values. Note that you can’t update contents of a fulltext field or a columnar attribute. If there’s such a need, use REPLACE.

Attribute updates are supported for RT, PQ and plain tables. All attribute types can be updated as long as they are stored in the traditional row-wise storage.

Note that document id cannot be updated.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. UPDATE products SET enabled=0 WHERE id=10;
  1. POST /update
  3. {
  4.   "index":"products",
  5.   "id":10,
  6.   "doc":
  7.   {
  8.     "enabled":0
  9.   }
  10. }
  1. $index->updateDocument([
  2.     'enabled'=>0
  3. ],10);
  1. indexApi = api = manticoresearch.IndexApi(client)
  2. indexApi.update({"index" : "products", "id" : 1, "doc" : {"price":10}})
  1. res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {"price":10}});
  1. UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
  2. doc = new HashMap<String,Object >(){{
  3.     put("price",10);
  4. }};
  5. updateRequest.index("products").id(1L).setDoc(doc);
  6. indexApi.update(updateRequest);

Response

  1. Query OK, 1 row affected (0.00 sec)
  1. {
  2.   "_index":"products",
  3.   "updated":1
  4. }
  1. Array(
  2.     [_index] => products
  3.     [_id] => 10
  4.     [result] => updated
  5. )
  1. {'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
  1. {"_index":"products","_id":1,"result":"updated"}
  1. class UpdateResponse {
  2.     index: products
  3.     updated: null
  4.     id: 1
  5.     result: updated
  6. }

Multiple attributes can be updated in a single statement.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. UPDATE products
  2. SET price=100000000000,
  3.     coeff=3465.23,
  4.     tags1=(3,6,4),
  5.     tags2=()
  6. WHERE MATCH('phone') AND enabled=1;
  1. POST /update
  2. {
  3.   "index":"products",
  4.   "doc":
  5.   {
  6.     "price":100000000000,
  7.     "coeff":3465.23,
  8.     "tags1":[3,6,4],
  9.     "tags2":[]
  10.   },
  11.   "query":
  12.   {
  13.     "match": { "*": "phone" },
  14.     "equals": { "enabled": 1 }
  15.   }
  16. }
  1. $query= new BoolQuery();
  2. $query->must(new Match('phone','*'));
  3. $query->must(new Equals('enabled',1));
  4. $index->updateDocuments([
  5.     'price' => 100000000000,
  6.     'coeff' => 3465.23,
  7.     'tags1' => [3,6,4],
  8.     'tags2' => []
  9.     ],
  10.     $query
  11. );
  1. indexApi = api = manticoresearch.IndexApi(client)
  2. indexApi.update({"index" : "products", "id" : 1, "doc" : {
  3.     "price": 100000000000,
  4.     "coeff": 3465.23,
  5.     "tags1": [3,6,4],
  6.     "tags2": []}})
  1. res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {
  2.     "price": 100000000000,
  3.     "coeff": 3465.23,
  4.     "tags1": [3,6,4],
  5.     "tags2": []}});
  1. UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
  2. doc = new HashMap<String,Object >(){{
  3.     put("price",10);
  4.     put("coeff",3465.23);
  5.     put("tags1",new int[]{3,6,4});
  6.     put("tags2",new int[]{});
  7. }};
  8. updateRequest.index("products").id(1L).setDoc(doc);
  9. indexApi.update(updateRequest);

Response

  1. Query OK, 148 rows affected (0.0 sec)
  1. {
  2.   "_index":"products",
  3.   "updated":148
  4. }
  1. Array(
  2.     [_index] => products
  3.     [updated] => 148
  4. )
  1. {'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
  1. {"_index":"products","_id":1,"result":"updated"}
  1. class UpdateResponse {
  2.     index: products
  3.     updated: null
  4.     id: 1
  5.     result: updated
  6. }

When assigning out-of-range values to 32-bit attributes, they will be trimmed to their lower 32 bits without a prompt. For example, if you try to update the 32-bit unsigned int with a value of 4294967297, the value of 1 will actually be stored, because the lower 32 bits of 4294967297 (0x100000001 in hex) amount to 1 (0x00000001 in hex).

UPDATE can be used to perform partial JSON updates on numeric data types or arrays of numeric data types. Just make sure you don’t update an integer value with a float value as it will be rounded off.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. insert into products (id, title, meta) values (1,'title','{"tags":[1,2,3]}');
  3. update products set meta.tags[0]=100 where id=1;
  1. POST /insert
  2. {
  3.     "index":"products",
  4.     "id":100,
  5.     "doc":
  6.     {
  7.         "title":"title",
  8.         "meta": {
  9.             "tags":[1,2,3]
  10.         }
  11.     }
  12. }
  14. POST /update
  15. {
  16.     "index":"products",
  17.     "id":100,
  18.     "doc":
  19.     {
  20.         "meta.tags[0]":100
  21.     }
  22. }
  1. $index->insertDocument([
  2.     'title' => 'title',
  3.     'meta' => ['tags' => [1,2,3]]
  4. ],1);
  5. $index->updateDocument([
  6.     'meta.tags[0]' => 100
  7. ],1);
  1. indexApi = api = manticoresearch.IndexApi(client)
  2. indexApi.update({"index" : "products", "id" : 1, "doc" : {
  3.     "meta.tags[0]": 100}})
  1. res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {
  2.    "meta.tags[0]": 100}});
  1. UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
  2. doc = new HashMap<String,Object >(){{
  3.     put("meta.tags[0]",100);
  4. }};
  5. updateRequest.index("products").id(1L).setDoc(doc);
  6. indexApi.update(updateRequest);

Response

  1. Query OK, 1 row affected (0.00 sec)
  3. Query OK, 1 row affected (0.00 sec)
  1. {
  2.    "_index":"products",
  3.    "_id":100,
  4.    "created":true,
  5.    "result":"created",
  6.    "status":201
  7. }
  9. {
  10.   "_index":"products",
  11.   "updated":1
  12. }
  1. Array(
  2.     [_index] => products
  3.     [_id] => 1
  4.     [created] => true
  5.     [result] => created
  6. )
  8. Array(
  9.     [_index] => products
  10.     [updated] => 1
  11. )
  1. {'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
  1. {"_index":"products","_id":1,"result":"updated"}
  1. class UpdateResponse {
  2.     index: products
  3.     updated: null
  4.     id: 1
  5.     result: updated
  6. }

Updating other data types or changing property type in a JSON attribute requires a full JSON update.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. insert into products values (1,'title','{"tags":[1,2,3]}');
  3. update products set data='{"tags":["one","two","three"]}' where id=1;
  1. POST /insert
  2. {
  3.     "index":"products",
  4.     "id":1,
  5.     "doc":
  6.     {
  7.         "title":"title",
  8.         "data":"{\"tags\":[1,2,3]}"
  9.     }
  10. }
  12. POST /update
  13. {
  14.     "index":"products",
  15.     "id":1,
  16.     "doc":
  17.     {
  18.         "data":"{\"tags\":[\"one\",\"two\",\"three\"]}"
  19.     }
  20. }
  1. $index->insertDocument([
  2.     'title'=> 'title',
  3.     'data' => [
  4.          'tags' => [1,2,3]
  5.     ]
  6. ],1);
  8. $index->updateDocument([
  9.     'data' => [
  10.             'one', 'two', 'three'
  11.     ]
  12. ],1);
  1. indexApi.insert({"index" : "products", "id" : 100, "doc" : {"title" : "title", "meta" : {"tags":[1,2,3]}}})
  2. indexApi.update({"index" : "products", "id" : 100, "doc" : {"meta" : {"tags":['one','two','three']}}})
  1. res = await indexApi.insert({"index" : "products", "id" : 100, "doc" : {"title" : "title", "meta" : {"tags":[1,2,3]}}});
  2. res = await indexApi.update({"index" : "products", "id" : 100, "doc" : {"meta" : {"tags":['one','two','three']}}});
  1. InsertDocumentRequest newdoc = new InsertDocumentRequest();
  2. doc = new HashMap<String,Object>(){{
  3.     put("title","title");
  4.     put("meta",
  5.         new HashMap<String,Object>(){{
  6.             put("tags",new int[]{1,2,3});
  7.         }});
  9. }};
  10. newdoc.index("products").id(100L).setDoc(doc);        
  11. indexApi.insert(newdoc);
  13. updatedoc = new UpdateDocumentRequest();
  14. doc = new HashMap<String,Object >(){{
  15.     put("meta",
  16.         new HashMap<String,Object>(){{
  17.             put("tags",new String[]{"one","two","three"});
  18.         }});
  19. }};
  20. updatedoc.index("products").id(100L).setDoc(doc);
  21. indexApi.update(updatedoc);

Response

  1. Query OK, 1 row affected (0.00 sec)
  3. Query OK, 1 row affected (0.00 sec)
  1. {
  2.   "_index":"products",
  3.   "updated":1
  4. }
  1. Array(
  2.     [_index] => products
  3.     [_id] => 1
  4.     [created] => true
  5.     [result] => created
  6. )
  8. Array(
  9.     [_index] => products
  10.     [updated] => 1
  11. )
  2. {'created': True,
  3.  'found': None,
  4.  'id': 100,
  5.  'index': 'products',
  6.  'result': 'created'}
  7. {'id': 100, 'index': 'products', 'result': 'updated', 'updated': None}
  1. {"_index":"products","_id":100,"created":true,"result":"created"}
  2. {"_index":"products","_id":100,"result":"updated"}
  1. class SuccessResponse {
  2.     index: products
  3.     id: 100
  4.     created: true
  5.     result: created
  6.     found: null
  7. }
  9. class UpdateResponse {
  10.     index: products
  11.     updated: null
  12.     id: 100
  13.     result: updated
  14. }

When using replication, the table name should be prepended with cluster_name: (in SQL) so that updates will be propagated to all nodes in the cluster. For queries via HTTP you should set a cluster property. See setting up replication for more info.

  1. {
  2.   "cluster":"nodes4",
  3.   "index":"test",
  4.   "id":1,
  5.   "doc":
  6.   {
  7.     "gid" : 100,
  8.     "price" : 1000
  9.   }
  10. }
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. update weekly:posts set enabled=0 where id=1;
  1. POST /update
  2. {
  3.     "cluster":"weekly",
  4.     "index":"products",
  5.     "id":1,
  6.     "doc":
  7.     {
  8.         "enabled":0
  9.     }
  10. }
  1. $index->setName('products')->setCluster('weekly');
  2. $index->updateDocument(['enabled'=>0],1);
  1. indexApi.update({"cluster":"weekly", "index" : "products", "id" : 1, "doc" : {"enabled" : 0}})
  1. res = wait indexApi.update({"cluster":"weekly", "index" : "products", "id" : 1, "doc" : {"enabled" : 0}});
  1. updatedoc = new UpdateDocumentRequest();
  2. doc = new HashMap<String,Object >(){{
  3.     put("enabled",0);
  4. }};
  5. updatedoc.index("products").cluster("weekly").id(1L).setDoc(doc);
  6. indexApi.update(updatedoc);

Response

  1. class UpdateResponse {
  2.     index: products
  3.     updated: null
  4.     id: 1
  5.     result: updated
  6. }

Updates via SQL

Here is the syntax for the SQL UPDATE statement:

  1. UPDATE table SET col1 = newval1 [, ...] WHERE where_condition [OPTION opt_name = opt_value [, ...]] [FORCE|IGNORE INDEX(id)]

where_condition has the same syntax as in the SELECT statement.

Multi-value attribute value sets must be specified as comma-separated lists in parentheses. To remove all values from a multi-value attribute, just assign () to it.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. UPDATE products SET tags1=(3,6,4) WHERE id=1;
  3. UPDATE products SET tags1=() WHERE id=1;
  1. POST /update
  3. {
  4.     "index":"products",
  5.     "_id":1,
  6.     "doc":
  7.     {
  8.         "tags1": []
  9.     }
  10. }
  1. $index->updateDocument(['tags1'=>[]],1);
  2. indexApi.update({"index" : "products", "id" : 1, "doc" : {"tags1": []}})
  2. indexApi.update({"index" : "products", "id" : 1, "doc" : {"tags1": []}})
  1. updatedoc = new UpdateDocumentRequest();
  2. doc = new HashMap<String,Object >(){{
  3.     put("tags1",new int[]{});
  4. }};
  5. updatedoc.index("products").id(1L).setDoc(doc);
  6. indexApi.update(updatedoc);

Response

  1. Query OK, 1 row affected (0.00 sec)
  3. Query OK, 1 row affected (0.00 sec)
  1. {
  2.   "_index":"products",
  3.   "updated":1
  4. }
  1. Array(
  2.     [_index] => products
  3.     [updated] => 1
  4. )
  1. {'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}
  1. {"_index":"products","_id":1,"result":"updated"}
  1. class UpdateResponse {
  2.     index: products
  3.     updated: null
  4.     id: 1
  5.     result: updated
  6. }

OPTION clause is a Manticore-specific extension that lets you control a number of per-update options. The syntax is:

  1. OPTION <optionname>=<value> [ , ... ]

The options are the same as for SELECT statement. Specifically for UPDATE statement you can use these options:

  • ‘ignore_nonexistent_columns’ - If set to 1 points that the update will silently ignore any warnings about trying to update a column which is not exists in current table schema. Default value is 0.
  • ‘strict’ - this option is used in partial JSON attribute updates. By default (strict=1), UPDATE will end in an error if the UPDATE query tries to perform an update on non-numeric properties. With strict=0 if multiple properties are updated and some are not allowed, the UPDATE will not end in error and will perform the changes only on allowed properties (with the rest being ignored). If none of the SET changes of the UPDATE are not permitted, the command will end in an error even with strict=0.

Query optimizer hints

In rare cases, Manticore’s built-in query analyzer may be incorrect in understanding a query and determining whether a table by ID should be used. This can result in poor performance for queries like UPDATE ... WHERE id = 123. For information on how to force the optimizer to use a docid index, see Query optimizer hints.

Updates via HTTP JSON

Updates using HTTP JSON protocol are performed via the /update endpoint. The syntax is similar to the /insert endpoint, but this time the doc property is mandatory.

The server will respond with a JSON object stating if the operation was successful or not.

  • JSON

JSON

  1. POST /update
  2. {
  3.   "index":"test",
  4.   "id":1,
  5.   "doc":
  6.    {
  7.      "gid" : 100,
  8.      "price" : 1000
  9.     }
  10. }

Response

  1. {
  2.   "_index": "test",
  3.   "_id": 1,
  4.   "result": "updated"
  5. }

The id of the document that needs to be updated can be set directly using the id property (as in the example above) or you can do an update by query and apply the update to all the documents that match the query:

  • JSON

JSON

  1. POST /update
  3. {
  4.   "index":"test",
  5.   "doc":
  6.   {
  7.     "price" : 1000
  8.   },
  9.   "query":
  10.   {
  11.     "match": { "*": "apple" }
  12.   }
  13. }

Response

  1. {
  2.   "_index":"products",
  3.   "updated":1
  4. }

The query syntax is the same as in the /search endpoint. Note that you can’t specify id and query at the same time.

Flushing attributes

  1. FLUSH ATTRIBUTES

Flushes all in-memory attribute updates in all the active disk tables to disk. Returns a tag that identifies the result on-disk state (basically, a number of actual disk attribute saves performed since the server startup).

  1. mysql> UPDATE testindex SET channel_id=1107025 WHERE id=1;
  2. Query OK, 1 row affected (0.04 sec)
  4. mysql> FLUSH ATTRIBUTES;
  5. +------+
  6. | tag  |
  7. +------+
  8. |    1 |
  9. +------+
  10. 1 row in set (0.19 sec)

See also attr_flush_period setting.

Bulk updates

Several update operations can be performed in a single call using the /bulk endpoint. This endpoint only works with data that has Content-Type set to 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 a \r.

  • JSON

JSON

  1. POST /bulk
  3. { "update" : { "index" : "products", "id" : 1, "doc": { "price" : 10 } } }
  4. { "update" : { "index" : "products", "id" : 2, "doc": { "price" : 20 } } }

Response

  1. {
  2.    "items":
  3.    [
  4.       {
  5.          "update":
  6.          {
  7.             "_index":"products",
  8.             "_id":1,
  9.             "result":"updated"
  10.          }
  11.       },
  12.       {
  13.          "update":
  14.          {
  15.             "_index":"products",
  16.             "_id":2,
  17.             "result":"updated"
  18.          }
  19.       }
  20.    ],
  21.    "errors":false
  22. }

/bulk endpoint supports inserts, replaces and deletes. Each statement starts with an action type (in this case, update). Here’s a list of the supported actions:

  • insert: Inserts a document. The syntax is the same as in the /insert endpoint.
  • create: a synonym for insert
  • replace: Replaces a document. The syntax is the same as in the /replace.
  • index: a synonym for replace
  • update: Updates a document. The syntax is the same as in the /update.
  • delete: Deletes a document. The syntax is the same as in the /delete endpoint.

Updates by query and deletes by query are also supported.

  • JSON
  • PHP
  • Python
  • javascript
  • Java

JSON PHP Python javascript Java

  1. POST /bulk
  3. { "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } }
  4. { "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } }
  2. $client->bulk([
  3.     ['update'=>[
  4.             'index' => 'products',
  5.              'doc' => [
  6.                 'coeff' => 100
  7.             ],
  8.             'query' => [
  9.                 'range' => ['price'=>['gte'=>1000]]
  10.             ]   
  11.         ]
  12.     ],
  13.     ['update'=>[
  14.             'index' => 'products',
  15.              'doc' => [
  16.                 'coeff' => 0
  17.             ],
  18.             'query' => [
  19.                 'range' => ['price'=>['lt'=>1000]]
  20.             ]   
  21.         ]
  22.     ]
  23. ]);
  1. docs = [ \
  2.             { "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } }, \
  3.             { "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } } ]
  4. indexApi.bulk('\n'.join(map(json.dumps,docs)))
  1. docs = [
  2.             { "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } },
  3.             { "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } } ];
  4. res =  await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));
  1. String   body = "{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 1000 }, \"query\": { \"range\": { \"price\": { \"gte\": 1000 } } } }} "+"\n"+
  2.     "{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 0 }, \"query\": { \"range\": { \"price\": { \"lt\": 1000 } } } } }"+"\n";         
  3. indexApi.bulk(body);

Response

  1. {
  2.   "items":
  3.   [
  4.     {
  5.       "update":
  6.       {
  7.         "_index":"products",
  8.         "updated":0
  9.       }
  10.     },
  11.     {
  12.       "update":
  13.       {
  14.         "_index":"products",
  15.         "updated":3
  16.       }
  17.     }
  18.   ],
  19.   "errors":false
  20. }
  1. Array(
  2.     [items] => Array (
  3.         Array(
  4.             [update] => Array(
  5.                 [_index] => products
  6.                 [updated] => 0
  7.             )
  8.         )   
  9.         Array(
  10.              [update] => Array(
  11.                  [_index] => products
  12.                  [updated] => 3
  13.              )
  14.         )    
  15. )
  1. {'error': None,
  2.  'items': [{u'update': {u'_index': u'products', u'updated': 1}},
  3.            {u'update': {u'_index': u'products', u'updated': 3}}]}
  1. {"items":[{"update":{"_index":"products","updated":1}},{"update":{"_index":"products","updated":5}}],"errors":false}
  1. class BulkResponse {
  2.     items: [{replace={_index=products, _id=1, created=false, result=updated, status=200}}, {replace={_index=products, _id=2, created=false, result=updated, status=200}}]
  3.     error: null
  4.     additionalProperties: {errors=false}
  5. }

Note that the bulk operation stops at the first query that results in an error.

attr_update_reserve

  1. attr_update_reserve=size

attr_update_reserve is a per-table setting which sets the space to be reserved for blob attribute updates. Optional, default value is 128k.

When blob attributes (MVAs, strings, JSON), are updated, their length may change. If the updated string (or MVA, or JSON) is shorter than the old one, it overwrites the old one in the .spb file. But if the updated string is longer, updates are written to the end of the .spb file. This file is memory mapped, that’s why resizing it may be a rather slow process, depending on the OS implementation of memory mapped files.

To avoid frequent resizes, you can specify the extra space to be reserved at the end of the .spb file by using this option.

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java
  • CONFIG

SQL JSON PHP Python javascript Java CONFIG

  1. create table products(title text, price float) attr_update_reserve = '1M'
  1. POST /cli -d "
  2. create table products(title text, price float) attr_update_reserve = '1M'"
  1. $params = [
  2.     'body' => [
  3.         'settings' => [
  4.             'attr_update_reserve' => '1M'
  5.         ],
  6.         'columns' => [
  7.             'title'=>['type'=>'text'],
  8.             'price'=>['type'=>'float']
  9.         ]
  10.     ],
  11.     'index' => 'products'
  12. ];
  13. $index = new \Manticoresearch\Index($client);
  14. $index->create($params);
  1. utilsApi.sql('create table products(title text, price float) attr_update_reserve = \'1M\'')
  1. res = await utilsApi.sql('create table products(title text, price float) attr_update_reserve = \'1M\'');
  1. utilsApi.sql("create table products(title text, price float) attr_update_reserve = '1M'");
  1. table products {
  2.   attr_update_reserve = 1M
  3.   type = rt
  4.   path = tbl
  5.   rt_field = title
  6.   rt_attr_uint = price
  7. }

attr_flush_period

  1. attr_flush_period = 900 # persist updates to disk every 15 minutes

When updating attributes the changes are first written to in-memory copy of attributes. This setting allows to set the interval between flushing the updates to disk. It defaults to 0, which disables the periodic flushing, but flushing will still occur at normal shut-down.

Deleting documents

Deleting is only supported for:

  • real-time tables,
  • percolate tables
  • distributed tables that contain only RT tables as agents

You can delete existing rows (documents) from an existing table based on ID or conditions.

Deleting documents is supported via SQL and HTTP interfaces.

SQL response for successful operation will show the number of rows deleted.

json/delete is an HTTP endpoint for deleting. The server will respond with a JSON object stating if the operation was successful or not and the number of rows deleted.

To delete all documents from a table it’s recommended to use instead the table truncation as it’s a much faster operation.

  • SQL
  • JSON

SQL JSON

  1. DELETE FROM table WHERE where_condition
  • table is a name of the table from which the row should be deleted.
  • where_condition for SQL has the same syntax as in the SELECT statement.
  1. POST /delete -d '
  2.     {
  3.      "index": "test",
  4.      "id": 1
  5.     }'
  6. POST /delete -d '
  7.     {
  8.         "index": "test",
  9.         "query":
  10.         {
  11.             "match": { "*": "apple" }
  12.         }
  13.     }'
  • id for JSON is the row id which should be deleted.
  • query for JSON is the full-text condition and has the same syntax as in the JSON/update.
  • cluster for JSON is cluster name property and should be set along with table property to delete a row from a table which is inside a replication cluster.

In this example we are deleting all documents that match full-text query dummy from table named test:

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. SELECT * FROM TEST;
  3. DELETE FROM TEST WHERE MATCH ('dummy');
  5. SELECT * FROM TEST;
  1. POST /delete -d '
  2.     {
  3.         "index":"test",
  4.         "query":
  5.         {
  6.             "match": { "*": "dummy" }
  7.         }
  8.     }'
  1. $index->deleteDocuments(new Match('dummy','*'));
  1. indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}})
  1. res = await indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}});
  1. DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
  2. query = new HashMap<String,Object>();
  3. query.put("match",new HashMap<String,Object>(){{
  4.     put("*","dummy");
  5. }});
  6. deleteRequest.index("products").setQuery(query); 
  7. indexApi.delete(deleteRequest);

Response

  1. +------+------+-------------+------+
  2. | id   | gid  | mva1        | mva2 |
  3. +------+------+-------------+------+
  4. |  100 | 1000 | 100,201     | 100  |
  5. |  101 | 1001 | 101,202     | 101  |
  6. |  102 | 1002 | 102,203     | 102  |
  7. |  103 | 1003 | 103,204     | 103  |
  8. |  104 | 1004 | 104,204,205 | 104  |
  9. |  105 | 1005 | 105,206     | 105  |
  10. |  106 | 1006 | 106,207     | 106  |
  11. |  107 | 1007 | 107,208     | 107  |
  12. +------+------+-------------+------+
  13. 8 rows in set (0.00 sec)
  15. Query OK, 2 rows affected (0.00 sec)
  17. +------+------+-------------+------+
  18. | id   | gid  | mva1        | mva2 |
  19. +------+------+-------------+------+
  20. |  100 | 1000 | 100,201     | 100  |
  21. |  101 | 1001 | 101,202     | 101  |
  22. |  102 | 1002 | 102,203     | 102  |
  23. |  103 | 1003 | 103,204     | 103  |
  24. |  104 | 1004 | 104,204,205 | 104  |
  25. |  105 | 1005 | 105,206     | 105  |
  26. +------+------+-------------+------+
  27. 6 rows in set (0.00 sec)
  1.     {
  2.         "_index":"test",
  3.         "deleted":2,
  4.     }
  1. Array(
  2.     [_index] => test
  3.     [deleted] => 2
  4. )
  1. {'deleted': 2, 'id': None, 'index': 'products', 'result': None}
  1. {"_index":"products","deleted":2}
  1. class DeleteResponse {
  2.     index: products
  3.     deleted: 2
  4.     id: null
  5.     result: null
  6. }

Here - deleting a document with id 100 from table named test:

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. DELETE FROM TEST WHERE id=100;
  3. SELECT * FROM TEST;
  1. POST /delete -d '
  2.     {
  3.         "index":"test",
  4.         "id": 100
  5.     }'
  1. $index->deleteDocument(100);
  1. indexApi.delete({"index" : "products", "id" : 1})
  1. res = await indexApi.delete({"index" : "products", "id" : 1});
  1. DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
  2. deleteRequest.index("products").setId(1L); 
  3. indexApi.delete(deleteRequest);

Response

  1. Query OK, 1 rows affected (0.00 sec)
  3. +------+------+-------------+------+
  4. | id   | gid  | mva1        | mva2 |
  5. +------+------+-------------+------+
  6. |  101 | 1001 | 101,202     | 101  |
  7. |  102 | 1002 | 102,203     | 102  |
  8. |  103 | 1003 | 103,204     | 103  |
  9. |  104 | 1004 | 104,204,205 | 104  |
  10. |  105 | 1005 | 105,206     | 105  |
  11. +------+------+-------------+------+
  12. 5 rows in set (0.00 sec)
  1.     {
  2.         "_index":"test",
  3.         "_id":100,
  4.         "found":true,
  5.         "result":"deleted"      
  6.     }
  1. Array(
  2.     [_index] => test
  3.     [_id] => 100
  4.     [found] => true
  5.     [result] => deleted
  6. )
  1. {'deleted': None, 'id': 1, 'index': 'products', 'result': 'deleted'}
  1. {"_index":"products","_id":1,"result":"deleted"}
  1. class DeleteResponse {
  2.     index: products
  3.     _id: 1
  4.     result: deleted
  5. }

Manticore SQL allows to use complex conditions for the DELETE statement.

For example here we are deleting documents that match full-text query dummy and have attribute mva1 with a value greater than 206 or mva1 values 100 or 103 from table named test:

  • SQL

SQL

  1. DELETE FROM TEST WHERE MATCH ('dummy') AND ( mva1>206 or mva1 in (100, 103) );
  3. SELECT * FROM TEST;

Response

  1. Query OK, 4 rows affected (0.00 sec)
  3. +------+------+-------------+------+
  4. | id   | gid  | mva1        | mva2 |
  5. +------+------+-------------+------+
  6. |  101 | 1001 | 101,202     | 101  |
  7. |  102 | 1002 | 102,203     | 102  |
  8. |  104 | 1004 | 104,204,205 | 104  |
  9. |  105 | 1005 | 105,206     | 105  |
  10. +------+------+-------------+------+
  11. 6 rows in set (0.00 sec)

Here is an example of deleting documents in cluster nodes4‘s table test:

  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java

SQL JSON PHP Python javascript Java

  1. delete from nodes4:test where id=100;
  1. POST /delete -d '
  2.     {
  3.       "cluster":"nodes4",
  4.       "index":"test",
  5.       "id": 100
  6.     }'
  1. $index->setCluster('nodes4');
  2. $index->deleteDocument(100);
  1. indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})
  1. indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})
  1. DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
  2. deleteRequest.cluster("nodes4").index("products").setId(100L); 
  3. indexApi.delete(deleteRequest);

Response

  1. Array(
  2.     [_index] => test
  3.     [_id] => 100
  4.     [found] => true
  5.     [result] => deleted
  6. )
  1. {'deleted': None, 'id': 100, 'index': 'products', 'result': 'deleted'}
  1. {"_index":"products","_id":100,"result":"deleted"}
  1. class DeleteResponse {
  2.     index: products
  3.     _id: 100
  4.     result: deleted
  5. }