✔ Updating documents

REPLACE vs UPDATE

You can change existing data in an RT or PQ table by either updating or replacing it.

UPDATE replaces row-wise attribute values of existing documents with new values. Full-text fields and columnar attributes cannot be updated. If you need to change the content of a full-text field or columnar attributes, use REPLACE.

REPLACE works similar to INSERT except that if an old document has the same ID as the new document, the old document is marked as deleted before the new document is inserted. Note that the old document does not get physically deleted from the table. The deletion can only happen when chunks are merged in a table, e.g. as a result of an OPTIMIZE.

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. }