Deleting documents

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.

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