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:
DELETE FROM index WHERE where_condition
POST /delete -d '
    {
     "index": "test",
     "id": 1
    }'
POST /delete -d '
    {
        "index": "test",
        "query":
        {
            "match": { "*": "apple" }
        }
    }'

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

SQL:
SELECT * FROM TEST;

DELETE FROM TEST WHERE MATCH ('dummy');

SELECT * FROM TEST;
+------+------+-------------+------+
| 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)
POST /delete -d '
    {
        "index":"test",
        "query":
        {
            "match": { "*": "dummy" }
        }
    }'
    {
        "_index":"test",
        "deleted":2,
    }
PHP:
$index->deleteDocuments(new Match('dummy','*'));
Array(
    [_index] => test
    [deleted] => 2
)
Python:
indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}})
{'deleted': 2, 'id': None, 'index': 'products', 'result': None}
javascript:
res = await indexApi.delete({"index" : "products", "query": { "match": { "*": "dummy" }}});
{"_index":"products","deleted":2}
java:
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);
      
class DeleteResponse {
    index: products
    deleted: 2
    id: null
    result: null
}

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

SQL:
DELETE FROM TEST WHERE id=100;

SELECT * FROM TEST;
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)
POST /delete -d '
    {
        "index":"test",
        "id": 100
    }'
    {
        "_index":"test",
        "_id":100,
        "found":true,
        "result":"deleted"      
    }
PHP:
$index->deleteDocument(100);
Array(
    [_index] => test
    [_id] => 100
    [found] => true
    [result] => deleted
)
Python:
indexApi.delete({"index" : "products", "id" : 1})
{'deleted': None, 'id': 1, 'index': 'products', 'result': 'deleted'}
javascript:
res = await indexApi.delete({"index" : "products", "id" : 1});
{"_index":"products","_id":1,"result":"deleted"}
java:
DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
deleteRequest.index("products").setId(1L); 
indexApi.delete(deleteRequest);
      
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;
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:
delete from nodes4:test where id=100;
POST /delete -d '
    {
      "cluster":"nodes4",
      "index":"test",
      "id": 100
    }'
PHP:
$index->setCluster('nodes4');
$index->deleteDocument(100);
Array(
    [_index] => test
    [_id] => 100
    [found] => true
    [result] => deleted
)
Python:
indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})
{'deleted': None, 'id': 100, 'index': 'products', 'result': 'deleted'}
javascript:
indexApi.delete({"cluster":"nodes4","index" : "products", "id" : 100})
{"_index":"products","_id":100,"result":"deleted"}
java:
DeleteDocumentRequest deleteRequest = new DeleteDocumentRequest();
deleteRequest.cluster("nodes4").index("products").setId(100L); 
indexApi.delete(deleteRequest);
      
class DeleteResponse {
    index: products
    _id: 100
    result: deleted
}