Filter operators for tables (TypeScript)

Many Data API commands, like Find rows (TypeScript), accept a filter parameter to determine which rows to return or update. The filter parameter uses one or more filter operators.

You can use filter operators on all supported types except map, list, set, and vector. For information about vector search on tables, see Sort clauses for tables (TypeScript).

Operators

You can use the following operators in a filter. All operators are case-sensitive.

Equal (default)

The $eq operator matches rows where the specified column’s value is equal to the specified value.

This is the default when you do not specify an operator.

This is the only filter operator allowed in updateOne and deleteOne.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({ number_of_pages: { $eq: 714 } });

  console.log(result);
})();

Not equal

The $ne operator matches rows where the specified column’s value is not equal to the specified value.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({ number_of_pages: { $ne: 300 } });

  console.log(result);
})();

Greater than

The $gt operator matches rows where the specified column’s value is greater than the specified value.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({ number_of_pages: { $gt: 300 } });

  console.log(result);
})();

Greater than or equal

The $gte operator matches rows where the specified column’s value is greater than or equal to the specified value.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({ number_of_pages: { $gte: 300 } });

  console.log(result);
})();

Less than

The $lt operator matches rows where the specified column’s value is less than the specified value.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({ number_of_pages: { $lt: 300 } });

  console.log(result);
})();

Less than or equal

The $lte operator matches rows where the specified column’s value is less than or equal to the specified value.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({ number_of_pages: { $lte: 300 } });

  console.log(result);
})();

In

The $in operator matches rows where the filtered column has a value equal to any of the specified values.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({
    author: { $in: ["Sara Jones", "Jennifer Ray"] },
  });

  console.log(result);
})();

Not in

The $nin operator matches rows that don’t contain any of the specified values.

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({
    author: { $nin: ["Sara Jones", "Jennifer Ray"] },
  });

  console.log(result);
})();

Combine operators (And, Or)

The $and operator joins clauses with a logical AND. Only rows that match the conditions of all clauses are returned.

The $or operator joins clauses with a logical OR. Rows that match the condition of any of the clauses are returned.

You can use $and and $or separately or together. If you want to match multiple flexible conditions, use $and and $or together in the same filter:

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({
    $and: [
      {
        $or: [{ is_checked_out: false }, { number_of_pages: { $lt: 300 } }],
      },
      {
        $or: [{ rating: { $lt: 4.3 } }, { publication_year: { $gte: 2002 } }],
      },
    ],
  });

  console.log(result);
})();

To filter on a range of values, use $and with $lt/$lte and $gt/$gte:

import {
  DataAPIClient,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

// Get an existing table
const client = new DataAPIClient({ environment: "hcd" });
const database = client.db("API_ENDPOINT", {
  token: new UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
  keyspace: "KEYSPACE_NAME",
});
const table = database.table("TABLE_NAME");

// Find a row
(async function () {
  const result = await table.findOne({
    $and: [
      { number_of_pages: { $lt: 300 } },
      { number_of_pages: { $gte: 200 } },
    ],
  });

  console.log(result);
})();