Deleting documents

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.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

REPLACE INTO products VALUES(1, "document one", 10);

POST /replace
{
  "index":"products",
  "id":1,
  "doc":
  {
    "title":"document one",
    "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);

‹›

Response

Query OK, 1 row affected (0.00 sec)

{
  "_index":"products",
  "_id":1,
  "created":false,
  "result":"updated",
  "status":200
}

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
}

REPLACE is supported for RT and PQ indexes.

The old document is not removed from the index, it is only marked as deleted. Because of this the index size grows until index 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:

REPLACE INTO index [(column1, column2, ...)]
    VALUES (value1, value2, ...)
    [, (...)]

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
HTTP
PHP
Python
javascript
Java

📋

REPLACE INTO products(id,title,tag) VALUES (1, 'doc one', 10), (2,' doc two', 20);

POST /bulk

{ "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);

‹›

Response

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

UPDATE

UPDATE changes attribute values of existing documents in a specified index with new values. Note that you can't update contents of a fulltext field. If there's a need to change contents of a full-text field, use REPLACE.

Attribute updates are supported for RT, PQ and plain indexes. All attribute types can be updated.

Note that document id cannot be updated.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

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);

‹›

Response

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
}

Multiple attributes can be updated in a single statement.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

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);

‹›

Response

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
}

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.

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

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);

‹›

Response

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
}

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

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

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);

‹›

Response

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
}

When using replication, index 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.

{
  "cluster":"nodes4",
  "index":"test",
  "id":1,
  "doc":
  {
    "gid" : 100,
    "price" : 1000
  }
}

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

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);

‹›

Response

class UpdateResponse {
    index: products
    updated: null
    id: 1
    result: updated
}

Here is the syntax for the SQL UPDATE statement:

UPDATE index SET col1 = newval1 [, ...] WHERE where_condition [OPTION opt_name = opt_value [, ...]]

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
HTTP
PHP
Python
javascript
Java

📋

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);

‹›

Response

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
}

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

Updates using HTTP protocol are performed via the /update endpoint. 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.

‹›

HTTP

HTTP

📋

⚙

POST /update
{
  "index":"test",
  "id":1,
  "doc":
   {
     "gid" : 100,
     "price" : 1000
    }
}

‹›

Response

{
  "_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 in the example above) or you can do an update by query and apply the update to all the documents that match the query:

‹›

HTTP

HTTP

📋

⚙

POST /update

{
  "index":"test",
  "doc":
  {
    "price" : 1000
  },
  "query":
  {
    "match": { "*": "apple" }
  }
}

‹›

Response

{
  "_index":"products",
  "updated":1
}

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

FLUSH ATTRIBUTES

Flushes all in-memory attribute updates in all the active disk indexes 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).

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.

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.

‹›

HTTP

HTTP

📋

⚙

POST /bulk

{ "update" : { "index" : "products", "id" : 1, "doc": { "price" : 10 } } }
{ "update" : { "index" : "products", "id" : 2, "doc": { "price" : 20 } } }

‹›

Response

{
   "items":
   [
      {
         "update":
         {
            "_index":"products",
            "_id":1,
            "result":"updated"
         }
      },
      {
         "update":
         {
            "_index":"products",
            "_id":2,
            "result":"updated"
         }
      }
   ],
   "errors":false
}

/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. Syntax is the same as in the /insert endpoint.
create: a synonym for insert
replace: Replaces a document. Syntax is the same as in the /replace.
index: a synonym for replace
update: Updates a document. Syntax is the same as in /update.
delete: Deletes a document. Syntax is the same as in /delete endpoint.

Updates by query and deletes by query are also supported.

‹›

HTTP
PHP
Python
javascript
Java

📋

⚙

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);

‹›

Response

Array(
    [items] => Array (
        Array(
            [update] => Array(
                [_index] => products
                [updated] => 0
            ) 
        )   
        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":5}}],"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}
}

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

attr_update_reserve=size

attr_update_reserve is a per-index 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
HTTP
PHP
Python
javascript
Java
CONFIG

📋

create table products(title text, price float) attr_update_reserve = '1M'

POST /sql -d "mode=raw&query=
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('mode=raw&query=create table products(title text, price float) attr_update_reserve = \'1M\'')

res = await utilsApi.sql('mode=raw&query=create table products(title text, price float) attr_update_reserve = \'1M\'');

utilsApi.sql("mode=raw&query=create table products(title text, price float) attr_update_reserve = '1M'");

index products {
  attr_update_reserve = 1M
  type = rt
  path = idx
  rt_field = title
  rt_attr_uint = price
}

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.

REPLACE ️ Deleting documents

Deleting is only supported for:

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

You can delete existing rows (documents) from an existing index 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 an index it's recommended to use instead the index truncation as it's a much faster operation.

‹›

SQL
HTTP

📋

DELETE FROM index WHERE where_condition

index is a name of the index from which the row should be deleted.
where_condition for SQL has the same syntax as in the SELECT statement.

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

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

SELECT * FROM TEST;

DELETE FROM TEST WHERE MATCH ('dummy');

SELECT * FROM TEST;

POST /delete -d '
    {
        "index":"test",
        "query":
        {
            "match": { "*": "dummy" }
        }
    }'

$index->deleteDocuments(new Match('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);

‹›

Response

+------+------+-------------+------+
| 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)

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
}

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

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

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);

‹›

Response

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)

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
}

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 index named test:

‹›

SQL

📋

DELETE FROM TEST WHERE MATCH ('dummy') AND ( mva1>206 or mva1 in (100, 103) );

SELECT * FROM TEST;

‹›

Response

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 nodes4's index test:

‹›

SQL
HTTP
PHP
Python
javascript
Java

📋

delete from nodes4:test where id=100;

POST /delete -d '
    {
      "cluster":"nodes4",
      "index":"test",
      "id": 100
    }'

$index->setCluster('nodes4');
$index->deleteDocument(100);

indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})

indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})

DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
deleteRequest.cluster("nodes4").index("products").setId(100L); 
indexApi.delete(deleteRequest);

‹›

Response

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
}

UPDATE ️ Transactions

Manticore supports basic transactions when performing deleting and insertion into real-time and percolate indexes. That is: each change to an index is first saved into an internal changeset, and then is actually committed to the index. By default each command is wrapped into an individual automatic transaction, making it transparent: you just 'insert' something, and can see the inserted result after it completes, having no care about transactions. However that behaviour 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 the REPLACE in that it does in-place attribute update).
ALTER - for updating index schema
TRUNCATE - for emptying a real-time index
ATTACH - for attaching a plain index to a real-time index
CREATE - for creating an index

SET AUTOCOMMIT = {0 | 1}

SET AUTOCOMMIT controls the autocommit mode in the active session. AUTOCOMMIT is set to 1 by default. With default you have not to care about transactions, since every statement that performs any changes on any index is implicitly wrapped into separate transaction. Setting it to 0 allows you to manage transactions manually. I.e., they will not be visible until you explicitly commit them.

Transactions are limited to a single RT or percolate index, and also limited in size. They are atomic, consistent, overly isolated, and durable. Overly isolated means that the changes are not only invisible to the concurrent transactions but even to the current session itself.

START TRANSACTION | BEGIN
COMMIT
ROLLBACK

BEGIN statement (or its START TRANSACTION alias) forcibly commits pending transaction, if any, and begins a new one.

COMMIT statement commits the current transaction, making all its changes permanent.

ROLLBACK statement rolls back the current transaction, canceling all its changes.

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)

Inserted value immediately visible by 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)

Here changes is NOT automatically committed. So, insertion is not visible even in the same session, since they're not committed. Also, despite absent BEGIN statement, transaction is implicitly started.

So, let's finally commit it:

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)

Now it is finished and visible.

Using BEGIN and COMMIT you can define bounds of transaction explicitly, so no need to care 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)

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SERIALIZABLE }

SET TRANSACTION statement does nothing. It was implemented to maintain compatibility with 3rd party MySQL client libraries, connectors, and frameworks that may need to run this statement when connecting. They just goes across syntax parser and then returns 'ok'. Nothing usable for your own programs, just a stubs to make third-party clients happy.

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

️ Deleting documents ️Searching

REPLACE

UPDATE

Updates via SQL

Updates via HTTP

Flushing attributes

Bulk updates

Settings related with updates

attr_update_reserve

attr_flush_period

Transactions

Automatic and manual mode

BEGIN, COMMIT, and ROLLBACK

Examples

Automatic commits (default)

Manual commits (autocommit=0)

Manual transaction

SET TRANSACTION