Deleting documents

Deleting documents is only supported in RT mode for the following table types:

  • Real-time tables
  • Percolate tables
  • Distributed tables that only contain RT tables as 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 JSON interfaces.

For SQL, the response for a successful operation will indicate the number of rows deleted.

For JSON, 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.

In this example we delete all documents that match full-text query test document from the table named test:

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java
  • C#
  • TypeScript
  • Go
📋
mysql> 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)

mysql> DELETE FROM TEST WHERE MATCH ('test document');
Query OK, 2 rows affected (0.00 sec)

mysql> 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  |
+------+------+-------------+------+
6 rows in set (0.00 sec)
‹›
Response
{
    "table":"test",
    "deleted":2,
}

Here - deleting a document with id equalling 1 from the table named test:

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java
  • C#
  • TypeScript
  • Go
📋
mysql> DELETE FROM TEST WHERE id=1;
Query OK, 1 rows affected (0.00 sec)
‹›
Response
{
    "table": "test",
    "_id": 1,
    "found": true,
    "result": "deleted"      
}

Here, documents with id matching values from the table named test are deleted:

Note that the delete forms with id=N or id IN (X,Y) are the fastest, as they delete documents without performing a search. Also note that the response contains only the id of the first deleted document in the corresponding _id field.

‹›
  • SQL
  • JSON
  • PHP
📋
DELETE FROM TEST WHERE id IN (1,2);
‹›
Response
Query OK, 2 rows affected (0.00 sec)

Manticore SQL allows to use complex conditions for the DELETE statement.

For example here we are deleting documents that match full-text query test document and have attribute mva1 with a value greater than 206 or mva1 values 100 or 103 from table named test:

‹›
  • SQL
SQL
📋
DELETE FROM TEST WHERE MATCH ('test document') 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 cluster's table test. Note that we must provide the cluster name property along with table property to delete a row from a table within a replication cluster:

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java
  • C#
  • TypeScript
  • Go
📋
delete from cluster:test where id=100;
‹›
Response
Array(
    [_index] => test
    [_id] => 100
    [found] => true
    [result] => deleted
)

Bulk deletion

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#
  • TypeScript
  • Go
📋
⚙
POST /bulk

{ "delete" : { "table" : "test", "id" : 1 } }
{ "delete" : { "table" : "test", "query": { "equals": { "int_data" : 20 } } } }
‹›
Response
{
   "items":
   [
      {
         "bulk":
         {
            "table":"test",
            "_id":0,
            "created":0,
            "deleted":2,
            "updated":0,
            "result":"created",
            "status":201
         }
      }
   ],
   "errors":false
}
UPDATE Transactions