Delete a row

This Astra DB Serverless feature is currently in public preview. Development is ongoing, and the features and functionality are subject to change. Astra DB Serverless, and the use of such, is subject to the DataStax Preview Terms.

The Data API tables commands are available through HTTP and the clients.

If you use a client, tables commands are available only in client versions 2.0-preview or later. For more information, see Data API client upgrade guide.

Find and delete one row in a table.

A row represents a single record of data in a table in an Astra DB Serverless database.

You use the Table class to work with rows through the Data API clients. For instructions to get a Table object, see Work with tables.

For general information about working with rows, including common operations and operators, see Work with rows.

For more information about the Data API and clients, see Get started with the Data API.

Python
TypeScript
Java
curl

For more information, see the Client reference.

Find a row by its primary key, and then delete it:

my_table.delete_one({"match_id": "fight7", "round": 2})

Parameters:

Name Type Summary

Name	Type	Summary
`filter`	`dict`	Describes the full primary key of the row. The row matching that primary key will be deleted. Only the the `$eq` operator is allowed in `delete_one`.
`general_method_timeout_ms`	`int \| None`	A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as `request_timeout_ms` and `timeout_ms` for convenience.

filter

dict

Describes the full primary key of the row. The row matching that primary key will be deleted.

Only the the $eq operator is allowed in delete_one.

general_method_timeout_ms

int | None

A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as request_timeout_ms and timeout_ms for convenience.

Full example script

from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

insert_result = my_table.insert_many(
    [
        {"match_id": "fight7", "round": 1, "winner": "Joy"},
        {"match_id": "fight7", "round": 2, "winner": "Kevin"},
        {"match_id": "fight7", "round": 3, "winner": "Lauretta"},
    ],
)

# Count the rows matching a certain filter
len(my_table.find({"match_id": "fight7"}).to_list())
# 3

# Delete a row belonging to the group
my_table.delete_one({"match_id": "fight7", "round": 2})

# Count again
len(my_table.find({"match_id": "fight7"}).to_list())
# 2

# Attempt the delete again (nothing to delete)
my_table.delete_one({"match_id": "fight7", "round": 2})

# The count is unchanged
len(my_table.find({"match_id": "fight7"}).to_list())
# 2

Example:

# Count the rows matching a certain filter
len(my_table.find({"match_id": "fight7"}).to_list())
# 3

# Delete a row belonging to the group
my_table.delete_one({"match_id": "fight7", "round": 2})

# Count again
len(my_table.find({"match_id": "fight7"}).to_list())
# 2

# Attempt the delete again (nothing to delete)
my_table.delete_one({"match_id": "fight7", "round": 2})

# The count is unchanged
len(my_table.find({"match_id": "fight7"}).to_list())
# 2

For more information, see the Client reference.

Find a row by its primary key, and then delete it:

await table.deleteOne({ matchId: 'fight7', round: 2 });

Parameters:

Name Type Summary

Name	Type	Summary
`filter`	`TableFilter`	Describes the full primary key of the row. The row matching that primary key will be deleted. Only the the `$eq` operator is allowed in `deleteOne`.
`timeout?`	`WithTimeout`	The client-side timeout for this operation.

filter

TableFilter

Describes the full primary key of the row. The row matching that primary key will be deleted.

Only the the $eq operator is allowed in deleteOne.

timeout?

WithTimeout

The client-side timeout for this operation.

Returns:

Promise<void> - A promise that resolves when the operation is complete.

Why void?

The deleteMany operation, as returned from the Data API, is always { deletedCount: -1 }, regardless of how many rows were deleted. Therefore, void is used.

Example:

Full script

import { CreateTableDefinition, DataAPIClient, InferTablePrimaryKey, InferTableSchema, timestamp, uuid, vector } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax, and then infer the type.
// For information about table typing and definitions, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

type TableSchema = InferTableSchema<typeof TableDefinition>;

(async function () {
  // Create a table with the given TableSchema type if a 'games' table doesn't already exist
  const table = await db.createTable<TableSchema>('games', { definition: TableDefinition, ifNotExists: true });

  // Insert some rows in an unordered fashion.
  await table.insertMany([
    { matchId: 'fight4', round: 1, winner: 'Victor' },
    { matchId: 'fight5', round: 1, winner: 'Adam' },
    { matchId: 'fight5', round: 2, winner: 'Betta' },
    { matchId: 'fight5', round: 3, winner: 'Caio' },
    { matchId: 'fight7', round: 1, winner: 'Joy' },
    { matchId: 'fight7', round: 2, winner: 'Kevin' },
    { matchId: 'fight7', round: 3, winner: 'Lauretta' },
  ]);

// Use deleteOne and deleteMany to remove rows from a table.

// deleteOne examples (with 'find' to demonstrate that the row was deleted)

  // Find rows where 'matchId' is 'fight7'
  await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

  // Delete one of the 'fight7' rows
  await table.deleteOne({ matchId: 'fight7', round: 2 });

  // Find the 'fight7' rows again
  // The deleted row should not be returned
  await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

  // Attempt to delete the same 'fight7' row again
  // Although the operation succeeds, nothing happens if the row was already deleted
  await table.deleteOne({ matchId: 'fight7', round: 2 });

  // Find the 'fight7' rows once again
  // The same rows are returned as the previous attempt because the row was already deleted
  await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

// deleteMany examples

  // To use deleteMany to delete one row, specify the full primary key
  await table.deleteMany({ matchId: 'fight4', round: 1 });

  // To delete part of a partition, use an inequality operator on the table's final 'partitionSort' column
  // This example deletes all rows where 'matchId' is 'fight5' and 'round' is greater than or equal to 5
  await table.deleteMany({ matchId: 'fight5', round: { $gte: 5 } });

  // To delete an entire partition, do not specify 'partitionSort' columns
  // This example deletes all rows where 'matchId' is 'fight6'
  await table.deleteMany({ matchId: 'fight7' });

  // To delete all rows in the table, pass an empty filter
  await table.deleteMany({});

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();

// Find rows where 'matchId' is 'fight7'
await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

// Delete one of the 'fight7' rows
await table.deleteOne({ matchId: 'fight7', round: 2 });

// Find the 'fight7' rows again
// The deleted row should not be returned
await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

// Attempt to delete the same 'fight7' row again
// Although the operation succeeds, nothing happens if the row was already deleted
await table.deleteOne({ matchId: 'fight7', round: 2 });

// Find the 'fight7' rows once again
// The same rows are returned as the previous attempt because the row was already deleted
await table.find({ matchId: 'fight7' }).toArray().then(rs => console.log(rs.length));

For more information, see the Client reference.

Find a row by its primary key, and then delete it:

Filter filter = and(
   eq("match_id", "fight7"),
   eq("round", 2));

// No options
tableRow.deleteOne(filter);

// Using Options
tableRow.deleteOne(filter, new TableDeleteOneOptions()
  .timeout(1000));

Parameters:

Name Type Summary

Name	Type	Summary
`filter`	`Filter`	Describes the row to delete by its primary key values. You cannot filter on non-primary keys. Only the the `$eq` operator is allowed in `deleteOne`. Filters can be instantiated with its constructor and specialized with method `where(..)` or leverage the class `Filters`
`options`	`TableDeleteOneOptions`	Operations to be applied to the delete operation like (mostly) timeout.

filter

Filter

Describes the row to delete by its primary key values.

You cannot filter on non-primary keys.

Only the the $eq operator is allowed in deleteOne.

Filters can be instantiated with its constructor and specialized with method where(..) or leverage the class Filters

options

TableDeleteOneOptions

Operations to be applied to the delete operation like (mostly) timeout.

Example:

package com.datastax.astra.client.tables;

import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.commands.TableUpdateOperation;
import com.datastax.astra.client.tables.commands.options.TableDeleteOneOptions;
import com.datastax.astra.client.tables.commands.options.TableUpdateOneOptions;
import com.datastax.astra.client.tables.definition.rows.Row;

import java.util.Set;

import static com.datastax.astra.client.core.query.Filters.and;
import static com.datastax.astra.client.core.query.Filters.eq;

public class DeleteOne {
 public static void main(String[] args) {
  Database db = new DataAPIClient("token").getDatabase("endpoint");

  Table<Row> tableRow = db.getTable("games");

  // Update
  Filter filter = and(
    eq("match_id", "fight7"),
    eq("round", 2));

  tableRow.deleteOne(filter);
  tableRow.deleteOne(filter, new TableDeleteOneOptions()
          .timeout(1000));

 }

}

Find a row by its primary key, and then delete it:

curl -sS -L -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/ASTRA_DB_TABLE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "deleteOne": {
    "filter": {
      "PRIMARY_KEY_COLUMN": "PRIMARY_KEY_VALUE"
    }
  }
}' | jq

Parameters:

Name Type Summary

Name	Type	Summary
`deleteOne`	`command`	The Data API command to find and delete the first row in a table that matches the given `filter` criteria. If there is no match, then no action is taken.
`filter`	`object`	Key-value pairs describing the full primary key of the row to delete. Only the the `$eq` operator is allowed in `deleteOne`.

deleteOne

command

The Data API command to find and delete the first row in a table that matches the given filter criteria. If there is no match, then no action is taken.

filter

object

Key-value pairs describing the full primary key of the row to delete.

Only the the $eq operator is allowed in deleteOne.

Returns:

A well-formed request returns "deletedCount": -1, regardless of whether a row was found or deleted. A well-formed request against an empty table still returns "deletedCount": -1.

Example response

{
  "status": {
    "deletedCount": -1
  }
}

Delete a row

Was this helpful?

Give Feedback