REPLACE works similarly to INSERT, but it marks the previous document with the same ID as deleted before inserting a new one.
For HTTP JSON protocol, two request formats are available: Manticore and Elasticsearch-like. You can find both examples in the provided examples.
- SQL
- JSON
- Elasticsearch
- PHP
- Python
- javascript
- Java
- C#
REPLACE INTO products VALUES(1, "document one", 10);POST /replace
-H "Content-Type: application/x-ndjson" -d '
{
"index":"products",
"id":1,
"doc":
{
"title":"product one",
"price":10
}
}
'
PUT /products/_doc/2
{
"title": "product two",
"price": 20
}
POST /products/_doc/
{
"title": "product three",
"price": 10
}$index->replaceDocument([
'title' => 'document one',
'price' => 10
],1);indexApi.replace({"index" : "products", "id" : 1, "doc" : {"title" : "document one","price":10}})res = await indexApi.replace({"index" : "products", "id" : 1, "doc" : {"title" : "document one","price":10}});docRequest = new InsertDocumentRequest();
HashMap<String,Object> doc = new HashMap<String,Object>(){{
put("title","document one");
put("price",10);
}};
docRequest.index("products").id(1L).setDoc(doc);
sqlresult = indexApi.replace(docRequest);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "document one");
doc.Add("price", 10);
InsertDocumentRequest docRequest = new InsertDocumentRequest(index: "products", id: 1, doc: doc);
var sqlresult = indexApi.replace(docRequest);Query OK, 1 row affected (0.00 sec){
"_index":"products",
"_id":1,
"created":false,
"result":"updated",
"status":200
}
{
"_id":2,
"_index":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
"failed":0,
"successful":1,
"total":1
},
"_type":"_doc",
"_version":1,
"result":"created"
}
{
"_id":2235747273424240642,
"_index":"products",
"_primary_term":1,
"_seq_no":0,
"_shards":{
"failed":0,
"successful":1,
"total":1
},
"_type":"_doc",
"_version":1,
"result":"created"
}Array(
[_index] => products
[_id] => 1
[created] => false
[result] => updated
[status] => 200
){'created': False,
'found': None,
'id': 1,
'index': 'products',
'result': 'updated'}{"_index":"products","_id":1,"result":"updated"}class SuccessResponse {
index: products
id: 1
created: false
result: updated
found: null
}
class SuccessResponse {
index: products
id: 1
created: false
result: updated
found: null
}
REPLACE is available for both RT and PQ tables.
When you run a REPLACE, the previous document is not removed, but it's marked as deleted, so the table size grows until chunk merging happens, and the marked documents won't be included. To force a chunk merge, use the OPTIMIZE statement.
The syntax of the REPLACE statement is the same as the INSERT statement syntax.
REPLACE INTO table [(column1, column2, ...)]
VALUES (value1, value2, ...)
[, (...)]To use the HTTP JSON interface with REPLACE, use the /replace endpoint. There's also a synonym endpoint, /index.
You can replace multiple documents at once. Check bulk adding documents for more information.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
REPLACE INTO products(id,title,tag) VALUES (1, 'doc one', 10), (2,' doc two', 20);POST /bulk
-H "Content-Type: application/x-ndjson" -d '
{ "replace" : { "index" : "products", "id":1, "doc": { "title": "doc one", "tag" : 10 } } }
{ "replace" : { "index" : "products", "id":2, "doc": { "title": "doc two", "tag" : 20 } } }
'$index->replaceDocuments([
[
'id' => 1,
'title' => 'document one',
'tag' => 10
],
[
'id' => 2,
'title' => 'document one',
'tag' => 20
]
);indexApi = manticoresearch.IndexApi(client)
docs = [ \
{"replace": {"index" : "products", "id" : 1, "doc" : {"title" : "document one"}}}, \
{"replace": {"index" : "products", "id" : 2, "doc" : {"title" : "document two"}}} ]
api_resp = indexApi.bulk('\n'.join(map(json.dumps,docs)))docs = [
{"replace": {"index" : "products", "id" : 1, "doc" : {"title" : "document one"}}},
{"replace": {"index" : "products", "id" : 2, "doc" : {"title" : "document two"}}} ];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));body = "{\"replace\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"document one\"}}}" +"\n"+
"{\"replace\": {\"index\" : \"products\", \"id\" : 2, \"doc\" : {\"title\" : \"document two\"}}}"+"\n" ;
indexApi.bulk(body);string body = "{\"replace\": {\"index\" : \"products\", \"id\" : 1, \"doc\" : {\"title\" : \"document one\"}}}" +"\n"+
"{\"replace\": {\"index\" : \"products\", \"id\" : 2, \"doc\" : {\"title\" : \"document two\"}}}"+"\n" ;
indexApi.Bulk(body);Query OK, 2 rows affected (0.00 sec){
"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
}Array(
[items] =>
Array(
Array(
[_index] => products
[_id] => 2
[created] => false
[result] => updated
[status] => 200
)
Array(
[_index] => products
[_id] => 2
[created] => false
[result] => updated
[status] => 200
)
)
[errors => false
){'error': None,
'items': [{u'replace': {u'_id': 1,
u'_index': u'products',
u'created': False,
u'result': u'updated',
u'status': 200}},
{u'replace': {u'_id': 2,
u'_index': u'products',
u'created': False,
u'result': u'updated',
u'status': 200}}]}
{"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}
class BulkResponse {
items: [{replace={_index=products, _id=1, created=false, result=updated, status=200}}, {replace={_index=products, _id=2, created=false, result=updated, status=200}}]
error: null
additionalProperties: {errors=false}
}class BulkResponse {
items: [{replace={_index=products, _id=1, created=false, result=updated, status=200}}, {replace={_index=products, _id=2, created=false, result=updated, status=200}}]
error: null
additionalProperties: {errors=false}
}UPDATE changes row-wise attribute values of existing documents in a specified table with new values. Note that you can't update the 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 the document ID cannot be updated.
Note that when you update an attribute, its secondary index gets disabled, so consider replacing the document instead.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
UPDATE products SET enabled=0 WHERE id=10;POST /update
{
"index":"products",
"id":10,
"doc":
{
"enabled":0
}
}$index->updateDocument([
'enabled'=>0
],10);indexApi = api = manticoresearch.IndexApi(client)
indexApi.update({"index" : "products", "id" : 1, "doc" : {"price":10}})res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {"price":10}});UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
put("price",10);
}};
updateRequest.index("products").id(1L).setDoc(doc);
indexApi.update(updateRequest);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("price", 10);
UpdateDocumentRequest updateRequest = new UpdateDocumentRequest(index: "products", id: 1, doc: doc);
indexApi.Update(updateRequest);Query OK, 1 row affected (0.00 sec){
"_index":"products",
"updated":1
}Array(
[_index] => products
[_id] => 10
[result] => updated
){'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}{"_index":"products","_id":1,"result":"updated"}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}Multiple attributes can be updated in a single statement. Example:
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
UPDATE products
SET price=100000000000,
coeff=3465.23,
tags1=(3,6,4),
tags2=()
WHERE MATCH('phone') AND enabled=1;POST /update
{
"index":"products",
"doc":
{
"price":100000000000,
"coeff":3465.23,
"tags1":[3,6,4],
"tags2":[]
},
"query":
{
"match": { "*": "phone" },
"equals": { "enabled": 1 }
}
}$query= new BoolQuery();
$query->must(new Match('phone','*'));
$query->must(new Equals('enabled',1));
$index->updateDocuments([
'price' => 100000000000,
'coeff' => 3465.23,
'tags1' => [3,6,4],
'tags2' => []
],
$query
);indexApi = api = manticoresearch.IndexApi(client)
indexApi.update({"index" : "products", "id" : 1, "doc" : {
"price": 100000000000,
"coeff": 3465.23,
"tags1": [3,6,4],
"tags2": []}})res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {
"price": 100000000000,
"coeff": 3465.23,
"tags1": [3,6,4],
"tags2": []}});UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
put("price",10);
put("coeff",3465.23);
put("tags1",new int[]{3,6,4});
put("tags2",new int[]{});
}};
updateRequest.index("products").id(1L).setDoc(doc);
indexApi.update(updateRequest);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("price", 10);
doc.Add("coeff", 3465.23);
doc.Add("tags1", new List<int> {3,6,4});
doc.Add("tags2", new List<int> {});
UpdateDocumentRequest updateRequest = new UpdateDocumentRequest(index: "products", id: 1, doc: doc);
indexApi.Update(updateRequest);
Query OK, 148 rows affected (0.0 sec){
"_index":"products",
"updated":148
}Array(
[_index] => products
[updated] => 148
){'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}{"_index":"products","_id":1,"result":"updated"}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}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
- C#
insert into products (id, title, meta) values (1,'title','{"tags":[1,2,3]}');
update products set meta.tags[0]=100 where id=1;POST /insert
{
"index":"products",
"id":100,
"doc":
{
"title":"title",
"meta": {
"tags":[1,2,3]
}
}
}
POST /update
{
"index":"products",
"id":100,
"doc":
{
"meta.tags[0]":100
}
}$index->insertDocument([
'title' => 'title',
'meta' => ['tags' => [1,2,3]]
],1);
$index->updateDocument([
'meta.tags[0]' => 100
],1);indexApi = api = manticoresearch.IndexApi(client)
indexApi.update({"index" : "products", "id" : 1, "doc" : {
"meta.tags[0]": 100}})res = await indexApi.update({"index" : "products", "id" : 1, "doc" : {
"meta.tags[0]": 100}});UpdateDocumentRequest updateRequest = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
put("meta.tags[0]",100);
}};
updateRequest.index("products").id(1L).setDoc(doc);
indexApi.update(updateRequest);Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("meta.tags[0]", 100);
UpdateDocumentRequest updateRequest = new UpdateDocumentRequest(index: "products", id: 1, doc: doc);
indexApi.Update(updateRequest);
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec){
"_index":"products",
"_id":100,
"created":true,
"result":"created",
"status":201
}
{
"_index":"products",
"updated":1
}Array(
[_index] => products
[_id] => 1
[created] => true
[result] => created
)
Array(
[_index] => products
[updated] => 1
){'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}{"_index":"products","_id":1,"result":"updated"}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}Updating other data types or changing property type in a JSON attribute requires a full JSON update.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
insert into products values (1,'title','{"tags":[1,2,3]}');
update products set data='{"tags":["one","two","three"]}' where id=1;POST /insert
{
"index":"products",
"id":1,
"doc":
{
"title":"title",
"data":"{\"tags\":[1,2,3]}"
}
}
POST /update
{
"index":"products",
"id":1,
"doc":
{
"data":"{\"tags\":[\"one\",\"two\",\"three\"]}"
}
}$index->insertDocument([
'title'=> 'title',
'data' => [
'tags' => [1,2,3]
]
],1);
$index->updateDocument([
'data' => [
'one', 'two', 'three'
]
],1);indexApi.insert({"index" : "products", "id" : 100, "doc" : {"title" : "title", "meta" : {"tags":[1,2,3]}}})
indexApi.update({"index" : "products", "id" : 100, "doc" : {"meta" : {"tags":['one','two','three']}}})res = await indexApi.insert({"index" : "products", "id" : 100, "doc" : {"title" : "title", "meta" : {"tags":[1,2,3]}}});
res = await indexApi.update({"index" : "products", "id" : 100, "doc" : {"meta" : {"tags":['one','two','three']}}});InsertDocumentRequest newdoc = new InsertDocumentRequest();
doc = new HashMap<String,Object>(){{
put("title","title");
put("meta",
new HashMap<String,Object>(){{
put("tags",new int[]{1,2,3});
}});
}};
newdoc.index("products").id(100L).setDoc(doc);
indexApi.insert(newdoc);
updatedoc = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
put("meta",
new HashMap<String,Object>(){{
put("tags",new String[]{"one","two","three"});
}});
}};
updatedoc.index("products").id(100L).setDoc(doc);
indexApi.update(updatedoc);
Dictionary<string, Object> meta = new Dictionary<string, Object>();
meta.Add("tags", new List<int> {1,2,3});
Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("title", "title");
doc.Add("meta", meta);
InsertDocumentRequest newdoc = new InsertDocumentRequest(index: "products", id: 100, doc: doc);
indexApi.Insert(newdoc);
meta = new Dictionary<string, Object>();
meta.Add("tags", new List<string> {"one","two","three"});
doc = new Dictionary<string, Object>();
doc.Add("meta", meta);
UpdateDocumentRequest updatedoc = new UpdateDocumentRequest(index: "products", id: 100, doc: doc);
indexApi.Update(updatedoc);Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec){
"_index":"products",
"updated":1
}Array(
[_index] => products
[_id] => 1
[created] => true
[result] => created
)
Array(
[_index] => products
[updated] => 1
)
{'created': True,
'found': None,
'id': 100,
'index': 'products',
'result': 'created'}
{'id': 100, 'index': 'products', 'result': 'updated', 'updated': None}{"_index":"products","_id":100,"created":true,"result":"created"}
{"_index":"products","_id":100,"result":"updated"}
class SuccessResponse {
index: products
id: 100
created: true
result: created
found: null
}
class UpdateResponse {
index: products
updated: null
id: 100
result: updated
}class SuccessResponse {
index: products
id: 100
created: true
result: created
found: null
}
class UpdateResponse {
index: products
updated: null
id: 100
result: updated
}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 information.
{
"cluster":"nodes4",
"index":"test",
"id":1,
"doc":
{
"gid" : 100,
"price" : 1000
}
}- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
update weekly:posts set enabled=0 where id=1;POST /update
{
"cluster":"weekly",
"index":"products",
"id":1,
"doc":
{
"enabled":0
}
}$index->setName('products')->setCluster('weekly');
$index->updateDocument(['enabled'=>0],1);indexApi.update({"cluster":"weekly", "index" : "products", "id" : 1, "doc" : {"enabled" : 0}})
res = wait indexApi.update({"cluster":"weekly", "index" : "products", "id" : 1, "doc" : {"enabled" : 0}});
updatedoc = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
put("enabled",0);
}};
updatedoc.index("products").cluster("weekly").id(1L).setDoc(doc);
indexApi.update(updatedoc);
Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("enabled", 0);
UpdateDocumentRequest updatedoc = new UpdateDocumentRequest(index: "products", cluster: "weekly", id: 1, doc: doc);
indexApi.Update(updatedoc);class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}Here is the syntax for the SQL UPDATE statement:
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
- C#
UPDATE products SET tags1=(3,6,4) WHERE id=1;
UPDATE products SET tags1=() WHERE id=1;POST /update
{
"index":"products",
"_id":1,
"doc":
{
"tags1": []
}
}$index->updateDocument(['tags1'=>[]],1);
indexApi.update({"index" : "products", "id" : 1, "doc" : {"tags1": []}})
indexApi.update({"index" : "products", "id" : 1, "doc" : {"tags1": []}})updatedoc = new UpdateDocumentRequest();
doc = new HashMap<String,Object >(){{
put("tags1",new int[]{});
}};
updatedoc.index("products").id(1L).setDoc(doc);
indexApi.update(updatedoc);
Dictionary<string, Object> doc = new Dictionary<string, Object>();
doc.Add("tags1", new List<int> {});
UpdateDocumentRequest updatedoc = new UpdateDocumentRequest(index: "products", id: 1, doc: doc);
indexApi.Update(updatedoc);Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec){
"_index":"products",
"updated":1
}Array(
[_index] => products
[updated] => 1
){'id': 1, 'index': 'products', 'result': 'updated', 'updated': None}{"_index":"products","_id":1,"result":"updated"}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}class UpdateResponse {
index: products
updated: null
id: 1
result: updated
}OPTION clause is a Manticore-specific extension that lets you control a number of per-update options. The syntax is:
OPTION <optionname>=<value> [ , ... ]The options are the same as for the SELECT statement. Specifically for the UPDATE statement, you can use these options:
- 'ignore_nonexistent_columns' - If set to 1, it indicates that the update will silently ignore any warnings about trying to update a column which does not exist in the current table schema. The default value is 0.
- 'strict' - This option is used in partial JSON attribute updates. By default (strict=1),
UPDATEwill result in an error if theUPDATEquery tries to perform an update on non-numeric properties. With strict=0, if multiple properties are updated and some are not allowed, theUPDATEwill not result in an error and will perform the changes only on allowed properties (with the rest being ignored). If none of theSETchanges of theUPDATEre permitted, the command will result in an error even with strict=0.
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 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
POST /update
{
"index":"test",
"id":1,
"doc":
{
"gid" : 100,
"price" : 1000
}
}{
"_index": "test",
"_id": 1,
"result": "updated"
}The ID of the document that needs to be updated can be set directly using the id property, as shown in the previous example, or you can update documents by query and apply the update to all the documents that match the query:
- JSON
POST /update
{
"index":"test",
"doc":
{
"price" : 1000
},
"query":
{
"match": { "*": "apple" }
}
}{
"_index":"products",
"updated":1
}The query syntax is the same as in the /search endpoint. Note that you can't specify id and query at the same time.
FLUSH ATTRIBUTESThe FLUSH ATTRIBUTES command flushes all in-memory attribute updates in all the active tables to disk. It returns a tag that identifies the result on-disk state, which represents the number of actual disk attribute saves performed since the server startup.
mysql> UPDATE testindex SET channel_id=1107025 WHERE id=1;
Query OK, 1 row affected (0.04 sec)
mysql> FLUSH ATTRIBUTES;
+------+
| tag |
+------+
| 1 |
+------+
1 row in set (0.19 sec)See also attr_flush_period setting.
You can perform multiple update operations 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 should be formatted as newline-delimited JSON (NDJSON). Essentially, this means that each line should contain exactly one JSON statement and end with a newline \n and, possibly, a \r.
- JSON
POST /bulk
{ "update" : { "index" : "products", "id" : 1, "doc": { "price" : 10 } } }
{ "update" : { "index" : "products", "id" : 2, "doc": { "price" : 20 } } }{
"items":
[
{
"update":
{
"_index":"products",
"_id":1,
"result":"updated"
}
},
{
"update":
{
"_index":"products",
"_id":2,
"result":"updated"
}
}
],
"errors":false
}The /bulk endpoint supports inserts, replaces, and deletes. Each statement begins 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 forinsertreplace: Replaces a document. The syntax is the same as in the /replace.index: a synonym forreplaceupdate: 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
- C#
POST /bulk
{ "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } }
{ "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } }
$client->bulk([
['update'=>[
'index' => 'products',
'doc' => [
'coeff' => 100
],
'query' => [
'range' => ['price'=>['gte'=>1000]]
]
]
],
['update'=>[
'index' => 'products',
'doc' => [
'coeff' => 0
],
'query' => [
'range' => ['price'=>['lt'=>1000]]
]
]
]
]);docs = [ \
{ "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } }, \
{ "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } } ]
indexApi.bulk('\n'.join(map(json.dumps,docs)))docs = [
{ "update" : { "index" : "products", "doc": { "coeff" : 1000 }, "query": { "range": { "price": { "gte": 1000 } } } } },
{ "update" : { "index" : "products", "doc": { "coeff" : 0 }, "query": { "range": { "price": { "lt": 1000 } } } } } ];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));String body = "{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 1000 }, \"query\": { \"range\": { \"price\": { \"gte\": 1000 } } } }} "+"\n"+
"{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 0 }, \"query\": { \"range\": { \"price\": { \"lt\": 1000 } } } } }"+"\n";
indexApi.bulk(body);string body = "{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 1000 }, \"query\": { \"range\": { \"price\": { \"gte\": 1000 } } } }} "+"\n"+
"{ \"update\" : { \"index\" : \"products\", \"doc\": { \"coeff\" : 0 }, \"query\": { \"range\": { \"price\": { \"lt\": 1000 } } } } }"+"\n";
indexApi.Bulk(body);{
"items":
[
{
"update":
{
"_index":"products",
"updated":1
}
},
{
"update":
{
"_index":"products",
"updated":3
}
}
],
"errors":false
}Array(
[items] => Array (
Array(
[update] => Array(
[_index] => products
[updated] => 1
)
)
Array(
[update] => Array(
[_index] => products
[updated] => 3
)
)
)
{'error': None,
'items': [{u'update': {u'_index': u'products', u'updated': 1}},
{u'update': {u'_index': u'products', u'updated': 3}}]}
{"items":[{"update":{"_index":"products","updated":1}},{"update":{"_index":"products","updated":3}}],"errors":false}
class BulkResponse {
items: [{update={_index=products, _id=1, created=false, result=updated, status=200}}, {update={_index=products, _id=2, created=false, result=updated, status=200}}]
error: null
additionalProperties: {errors=false}
}class BulkResponse {
items: [{update={_index=products, _id=1, created=false, result=updated, status=200}}, {update={_index=products, _id=2, created=false, result=updated, status=200}}]
error: null
additionalProperties: {errors=false}
}Keep in mind that the bulk operation stops at the first query that results in an error.
attr_update_reserve=sizeattr_update_reserve is a per-table setting that determines the space reserved for blob attribute updates. This setting is optional, with a default value of 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. However, if the updated string is longer, updates are written to the end of the .spb file. This file is memory-mapped, which means 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 using this option.
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
- CONFIG
create table products(title text, price float) attr_update_reserve = '1M'POST /cli -d "
create table products(title text, price float) attr_update_reserve = '1M'"$params = [
'body' => [
'settings' => [
'attr_update_reserve' => '1M'
],
'columns' => [
'title'=>['type'=>'text'],
'price'=>['type'=>'float']
]
],
'index' => 'products'
];
$index = new \Manticoresearch\Index($client);
$index->create($params);utilsApi.sql('create table products(title text, price float) attr_update_reserve = \'1M\'')res = await utilsApi.sql('create table products(title text, price float) attr_update_reserve = \'1M\'');utilsApi.sql("create table products(title text, price float) attr_update_reserve = '1M'");utilsApi.Sql("create table products(title text, price float) attr_update_reserve = '1M'");table products {
attr_update_reserve = 1M
type = rt
path = tbl
rt_field = title
rt_attr_uint = price
}attr_flush_period = 900 # persist updates to disk every 15 minutesWhen 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 is only supported for the following table types:
- Real-time tables,
- Percolate tables,
- Distributed tables that only contain RT tables a local or remote agents.
You can delete existing documents from a table based on either their ID or certain conditions.
Also, bulk deletion is available to delete multiple documents.
Deletion of documents can be accomplished via both SQL and HTTP interfaces.
For SQL, the response for a successful operation will indicate the number of rows deleted.
For HTTP, the json/delete endpoint is used. The server will respond with a JSON object indicating whether the operation was successful and the number of rows deleted.
It is recommended to use table truncation instead of deletion to delete all documents from a table, as it is a much faster operation.
- SQL
- JSON
DELETE FROM table WHERE where_conditiontableis a name of the table from which the row should be deleted.where_conditionfor SQL has the same syntax as in the SELECT statement.
POST /delete -d '
{
"index": "test",
"id": 1
}'
POST /delete -d '
{
"index": "test",
"query":
{
"match": { "*": "apple" }
}
}'idfor JSON is the rowidwhich should be deleted.queryfor JSON is the full-text condition and has the same syntax as in the JSON/update.clusterfor JSON is cluster name property and should be set along withtableproperty 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
- C#
SELECT * FROM TEST;
DELETE FROM TEST WHERE MATCH ('dummy');
SELECT * FROM TEST;POST /delete -d '
{
"index":"test",
"query":
{
"match": { "*": "dummy" }
}
}'$index->deleteDocuments(new MatchPhrase('dummy','*'));indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}})res = await indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}});DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
query = new HashMap<String,Object>();
query.put("match",new HashMap<String,Object>(){{
put("*","dummy");
}});
deleteRequest.index("products").setQuery(query);
indexApi.delete(deleteRequest);
Dictionary<string, Object> match = new Dictionary<string, Object>();
match.Add("*", "dummy");
Dictionary<string, Object> query = new Dictionary<string, Object>();
query.Add("match", match);
DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest(index: "products", query: query);
indexApi.Delete(deleteRequest);
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 100 | 1000 | 100,201 | 100 |
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 103 | 1003 | 103,204 | 103 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
| 106 | 1006 | 106,207 | 106 |
| 107 | 1007 | 107,208 | 107 |
+------+------+-------------+------+
8 rows in set (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 100 | 1000 | 100,201 | 100 |
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 103 | 1003 | 103,204 | 103 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
+------+------+-------------+------+
6 rows in set (0.00 sec) {
"_index":"test",
"deleted":2,
}Array(
[_index] => test
[deleted] => 2
){'deleted': 2, 'id': None, 'index': 'products', 'result': None}{"_index":"products","deleted":2}class DeleteResponse {
index: products
deleted: 2
id: null
result: null
}class DeleteResponse {
index: products
deleted: 2
id: null
result: null
}Here - deleting a document with id 100 from table named test:
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
DELETE FROM TEST WHERE id=100;
SELECT * FROM TEST;POST /delete -d '
{
"index":"test",
"id": 100
}'$index->deleteDocument(100);indexApi.delete({"index" : "products", "id" : 1})res = await indexApi.delete({"index" : "products", "id" : 1});DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
deleteRequest.index("products").setId(1L);
indexApi.delete(deleteRequest);
DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest(index: "products", id: 1);
indexApi.Delete(deleteRequest);
Query OK, 1 rows affected (0.00 sec)
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 103 | 1003 | 103,204 | 103 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
+------+------+-------------+------+
5 rows in set (0.00 sec) {
"_index":"test",
"_id":100,
"found":true,
"result":"deleted"
}Array(
[_index] => test
[_id] => 100
[found] => true
[result] => deleted
){'deleted': None, 'id': 1, 'index': 'products', 'result': 'deleted'}{"_index":"products","_id":1,"result":"deleted"}class DeleteResponse {
index: products
_id: 1
result: deleted
}class DeleteResponse {
index: products
_id: 1
result: deleted
}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
DELETE FROM TEST WHERE MATCH ('dummy') AND ( mva1>206 or mva1 in (100, 103) );
SELECT * FROM TEST;Query OK, 4 rows affected (0.00 sec)
+------+------+-------------+------+
| id | gid | mva1 | mva2 |
+------+------+-------------+------+
| 101 | 1001 | 101,202 | 101 |
| 102 | 1002 | 102,203 | 102 |
| 104 | 1004 | 104,204,205 | 104 |
| 105 | 1005 | 105,206 | 105 |
+------+------+-------------+------+
6 rows in set (0.00 sec)Here is an example of deleting documents in cluster cluster's table test:
- SQL
- JSON
- PHP
- Python
- javascript
- Java
- C#
delete from cluster:test where id=100;POST /delete -d '
{
"cluster":"cluster",
"index":"test",
"id": 100
}'$index->setCluster('cluster');
$index->deleteDocument(100);indexApi.delete({"cluster":"cluster","index" : "products", "id" : 100})indexApi.delete({"cluster":"cluster","index" : "products", "id" : 100})DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
deleteRequest.cluster("cluster").index("products").setId(100L);
indexApi.delete(deleteRequest);
DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest(index: "products", cluster: "cluster", id: 100);
indexApi.Delete(deleteRequest);
Array(
[_index] => test
[_id] => 100
[found] => true
[result] => deleted
){'deleted': None, 'id': 100, 'index': 'products', 'result': 'deleted'}{"_index":"products","_id":100,"result":"deleted"}class DeleteResponse {
index: products
_id: 100
result: deleted
}class DeleteResponse {
index: products
_id: 100
result: deleted
}You can also perform multiple delete operations 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 should be formatted as newline-delimited JSON (NDJSON). Essentially, this means that each line should contain exactly one JSON statement and end with a newline \n and, possibly, a \r.
- JSON
- PHP
- Python
- javascript
- Java
- C#
POST /bulk
{ "delete" : { "index" : "products", "id" : 1 } }
{ "delete" : { "index" : "products", "query": { "equals": { "price" : 20 } } } }
$client->bulk([
['delete' => [
'index' => 'products',
'id' => 1
]
],
['delete'=>[
'index' => 'products',
'query' => [
'equals' => ['price' => 20]
]
]
]
]);docs = [ \
{ "delete" : { "index" : "products", "id": 1 } }, \
{ "delete" : { "index" : "products", "query": { "equals": { "price": 20 } } } } ]
indexApi.bulk('\n'.join(map(json.dumps,docs)))docs = [
{ "delete" : { "index" : "products", "id": 1 } },
{ "delete" : { "index" : "products", "query": { "equals": { "price": 20 } } } } ];
res = await indexApi.bulk(docs.map(e=>JSON.stringify(e)).join('\n'));String body = "{ "delete" : { "index" : "products", "id": 1 } } "+"\n"+
"{ "delete" : { "index" : "products", "query": { "equals": { "price": 20 } } } }"+"\n";
indexApi.bulk(body);string body = "{ "delete" : { "index" : "products", "id": 1 } } "+"\n"+
"{ "delete" : { "index" : "products", "query": { "equals": { "price": 20 } } } }"+"\n";
indexApi.Bulk(body);{
"items":
[
{
"bulk":
{
"_index":"products",
"_id":0,
"created":0,
"deleted":2,
"updated":0,
"result":"created",
"status":201
}
}
],
"errors":false
}Array(
[items] => Array
(
[0] => Array
(
[bulk] => Array
(
[_index] => test
[_id] => 0
[created] => 0
[deleted] => 2
[updated] => 0
[result] => created
[status] => 201
)
)
)
[current_line] => 3
[skipped_lines] => 0
[errors] =>
[error] =>
)
{
'error': None,
'items': [{u'delete': {u'_index': u'products', u'deleted': 2}}]
}
{"items":[{"delete":{"_index":"products","deleted":2}}],"errors":false}
class BulkResponse {
items: [{delete={_index=products, _id=0, created=false, deleted=2, result=created, status=200}}]
error: null
additionalProperties: {errors=false}
}class BulkResponse {
items: [{replace={_index=products, _id=0, created=false, deleted=2, result=created, status=200}}]
error: null
additionalProperties: {errors=false}
}Manticore supports basic transactions for deleting and inserting data into real-time and percolate tables, except when attempting to write to a distributed table which includes a real-time or percolate table. Each change to a table is first saved in an internal changeset and then actually committed to the table. By default, each command is wrapped in an individual automatic transaction, making it transparent: you simply 'insert' something and can see the inserted result after it completes, without worrying about transactions. However, this behavior can be explicitly managed by starting and committing transactions manually.
Transactions are supported for the following commands:
Transactions are not supported for:
- UPDATE (which is different from REPLACE, as it performs an in-place attribute update)
- ALTER - for updating table schema
- TRUNCATE - for emptying a real-time table
- ATTACH - for attaching a plain table to a real-time table
- CREATE - for creating a table
- DROP - for deleting a table
Please note that transactions in Manticore do not aim to provide isolation. The purpose of transactions in Manticore is to allow you to accumulate multiple writes and execute them all at once upon commit, or to roll them all back if necessary. Transactions are integrated with binary log for durability and consistency.
SET AUTOCOMMIT = {0 | 1}SET AUTOCOMMIT controls the autocommit mode in the active session. AUTOCOMMIT is set to 1 by default. With the default setting, you don't have to worry about transactions, as every statement that makes any changes to any table is implicitly wrapped in a separate transaction. Setting it to 0 allows you to manage transactions manually, meaning they will not be visible until you explicitly commit them.
Transactions are limited to a single real-time or percolate table and are also limited in size. They are atomic, consistent, overly isolated, and durable. Overly isolated means that the changes are not only invisible to concurrent transactions but even to the current session itself.
START TRANSACTION | BEGIN
COMMIT
ROLLBACKThe BEGIN statement (or its START TRANSACTION alias) forcibly commits any pending transaction, if present, and starts a new one.
The COMMIT statement commits the current transaction, making all its changes permanent.
The ROLLBACK statement rolls back the current transaction, canceling all its changes.
When using one of the /bulk JSON endpoints ( bulk insert, bulk replace, bulk delete ), you can force a batch of documents to be committed by adding an empty line after them.
insert into indexrt (id, content, title, channel_id, published) values (1, 'aa', 'blabla', 1, 10);
Query OK, 1 rows affected (0.00 sec)
select * from indexrt where id=1;
+------+------------+-----------+--------+
| id | channel_id | published | title |
+------+------------+-----------+--------+
| 1 | 1 | 10 | blabla |
+------+------------+-----------+--------+
1 row in set (0.00 sec)The inserted value is immediately visible in the following 'select' statement.
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (3, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (4, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
select * from indexrt where id=3;
Empty set (0.01 sec)
select * from indexrt where id=4;
Empty set (0.00 sec)In this case, changes are NOT automatically committed. As a result, the insertions are not visible, even in the same session, since they have not been committed. Also, despite the absence of a BEGIN statement, a transaction is implicitly started.
To make the changes visible, you need to commit the transaction:
commit;
Query OK, 0 rows affected (0.00 sec)
select * from indexrt where id=4;
+------+------------+-----------+-------+
| id | channel_id | published | title |
+------+------------+-----------+-------+
| 4 | 1 | 1 | bb |
+------+------------+-----------+-------+
1 row in set (0.00 sec)
select * from indexrt where id=3;
+------+------------+-----------+-------+
| id | channel_id | published | title |
+------+------------+-----------+-------+
| 3 | 1 | 1 | bb |
+------+------------+-----------+-------+
1 row in set (0.00 sec)After the commit statement, the insertions are visible in the table.
By using BEGIN and COMMIT, you can define the bounds of a transaction explicitly, so there's no need to worry about autocommit in this case.
begin;
Query OK, 0 rows affected (0.00 sec)
insert into indexrt (id, content, title, channel_id, published) values (2, 'aa', 'bb', 1, 1);
Query OK, 1 row affected (0.00 sec)
select * from indexrt where id=2;
Empty set (0.01 sec)
commit;
Query OK, 0 rows affected (0.01 sec)
select * from indexrt where id=2;
+------+------------+-----------+-------+
| id | channel_id | published | title |
+------+------------+-----------+-------+
| 2 | 1 | 1 | bb |
+------+------------+-----------+-------+
1 row in set (0.01 sec)