Find a row

{
  'match_id': 'challenge6',
  'round': 1,
  'fighters': DataAPISet([]),
  'm_vector': DataAPIVector([0.9, -0.1, -0.3]),
  'score': None,
  'when': None,
  'winner': 'Donna'
}

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()
    ),
)

from astrapy.constants import VectorMetric
from astrapy.info import TableIndexOptions, TableVectorIndexOptions

my_table.create_index(
    "score_index",
    column="score",
)

my_table.create_index(
    "winner_index",
    column="winner",
    options=TableIndexOptions(
        ascii=False,
        normalize=True,
        case_sensitive=False,
    ),
)

my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
    ),
)

from astrapy.data_types import (
    DataAPISet,
    DataAPITimestamp,
    DataAPIVector,
)
from astrapy.ids import UUID

insert_result = my_table.insert_many(
    [
        {
            "match_id": "fight4",
            "round": 1,
            "winner": "Victor",
            "score": 18,
            "when": DataAPITimestamp.from_string(
                "2024-11-28T11:30:00Z",
            ),
            "fighters": DataAPISet([
                UUID("0193539a-2770-8c09-a32a-111111111111"),
                UUID('019353e3-00b4-83f9-a127-222222222222'),
            ]),
            "m_vector": DataAPIVector([0.4, -0.6, 0.2]),
        },
        {
            "match_id": "challenge6",
            "round": 1,
            "winner": "Donna",
            "m_vector": [0.9, -0.1, -0.3],
        },
        {"match_id": "challenge6", "round": 2, "winner": "Erick"},
        {"match_id": "challenge6", "round": 3, "winner": "Fiona"},
        {"match_id": "fight5", "round": 3, "winner": "Caio Gozer"},
    ],
)

from astrapy.constants import SortMode
from astrapy.data_types import DataAPITimestamp, DataAPIVector

# Filter on the partitioning:
my_table.find_one({"match_id": "challenge6"})
# {'match_id': 'challenge6', 'round': 1, 'fighters': DataAPISet([]), ...

# A find with no matches:
str(my_table.find_one({"match_id": "not_real"}))
# 'None'

# Optimize bandwidth using a projection:
my_table.find_one({"match_id": "challenge6"}, projection={"round": True, "winner": True})
# {'round': 1, 'winner': 'Donna'}

# Filter on primary key:
my_table.find_one({"match_id": "challenge6", "round": 1})
# {'match_id': 'challenge6', 'round': 1, 'fighters': DataAPISet([]), ...

# Filter on a regular indexed column:
my_table.find_one({"winner": "Caio Gozer"})
# {'match_id': 'fight5', 'round': 3, 'fighters': DataAPISet([]), ...

# Non-equality filter on a regular indexed column:
my_table.find_one({"score": {"$gte": 15}})
# {'match_id': 'fight4', 'round': 1, 'fighters': DataAPISet([UUID('0193...

# Filter on a regular non-indexed column:
# (not recommended performance-wise)
my_table.find_one(
    {"when": {
        "$gte": DataAPITimestamp.from_string("1999-12-31T01:23:44Z")
    }}
)
# The Data API returned a warning: {'errorCode': 'MISSING_INDEX', ...
# {'match_id': 'fight4', 'round': 1, 'fighters': DataAPISet([UUID('0193...

# Empty filter:
my_table.find_one({})
# The Data API returned a warning: {'errorCode': 'ZERO_FILTER_OPERATIONS', ...
# {'match_id': 'fight4', 'round': 1, 'fighters': DataAPISet([UUID('0193...

# Filter on the primary key and a regular non-indexed column:
# (not recommended performance-wise)
my_table.find_one(
    {"match_id": "fight5", "round": 3, "winner": "Caio Gozer"}
)
# The Data API returned a warning: {'errorCode': 'MISSING_INDEX', ...
# {'match_id': 'fight5', 'round': 3, 'fighters': DataAPISet([]), ...

# Filter on a regular non-indexed column (and incomplete primary key)
# (not recommended performance-wise)
my_table.find_one({"round": 3, "winner": "Caio Gozer"})
# The Data API returned a warning: {'errorCode': 'MISSING_INDEX', ...
# {'match_id': 'fight5', 'round': 3, 'fighters': DataAPISet([]), ...

# Vector search with "sort" (on an appropriately-indexed vector column):
my_table.find_one(
    {},
    sort={"m_vector": DataAPIVector([0.2, 0.3, 0.4])},
    projection={"winner": True},
)
# {'winner': 'Donna'}

# Hybrid search with vector sort and non-vector filtering:
my_table.find_one(
    {"match_id": "fight4"},
    sort={"m_vector": DataAPIVector([0.2, 0.3, 0.4])},
    projection={"winner": True},
)
# {'winner': 'Victor'}

# Return the numeric value of the vector similarity
# (also demonstrating that one can pass a plain list for a vector):
my_table.find_one(
    {},
    sort={"m_vector": [0.2, 0.3, 0.4]},
    projection={"winner": True},
    include_similarity=True,
)
# {'winner': 'Donna', '$similarity': 0.515}

# Non-vector sorting on a 'partitionSort' column:
my_table.find_one(
    {"match_id": "fight5"},
    sort={"round": SortMode.DESCENDING},
    projection={"winner": True},
)
# {'winner': 'Caio Gozer'}

# Non-vector sorting on a regular column:
# (not recommended performance-wise)
my_table.find_one(
    {"match_id": "fight5"},
    sort={"winner": SortMode.ASCENDING},
    projection={"winner": True},
)
# The Data API returned a warning: {'errorCode': 'IN_MEMORY_SORTING...
# {'winner': 'Adam Zuul'}

{
  match_id: "challenge6",
  round: 1,
  fighters: Set([]),
  mVector: DataAPIVector([0.9, -0.1, -0.3]),
  score: null,
  when: null,
  winner: "Donna",
}

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', score: 18, when: timestamp('2024-11-28T11:30:00Z'), fighters: new Set([UUID('0193539a-2770-8c09-a32a-111111111111'), UUID('019353e3-00b4-83f9-a127-222222222222')]), mVector: vector([0.4, -0.6, 0.2]) },
    { matchId: 'challenge6', round: 1, winner: 'Donna', mVector: vector([0.9, -0.1, -0.3]) },
    { matchId: 'challenge6', round: 2, winner: 'Erick' },
    { matchId: 'challenge6', round: 3, winner: 'Fiona' },
    { matchId: 'fight5', round: 3, winner: 'Caio Gozer' },
  ]);

  // Create a secondary index on the 'score' column if it doesn't already exist
    await table.createIndex('round_idx', 'round', { ifNotExists: true });

  // Create a secondary index on the 'winner' column with case-insensitivity if it doesn't already exist
  await table.createIndex('winner_idx', 'winner', {
    options: {
      caseSensitive: false,
    },
    ifNotExists: true,
  });

  // Create a vector index on the 'mVector' column if it doesn't already exist
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
    },
  });

// Use findOne and find to query rows in a table, including vector search.

// findOne examples

  // Find a row with an exact match on one column.
  // Inherently uses the equality ($eq) filter operator.
  await table.findOne({ 'matchId': 'challenge6' }).then(console.log);

  // If there is no match, the response is null.
  await table.findOne({ 'matchId': 'not_real' }).then(console.log);

  // Projections optimize bandwidth by returning specified columns instead of the entire row.
  // Specify the exact return type to prevent accidental type errors.
  // This example prints the values for 'round' and 'winner' in the matching row.
  await table.findOne<Pick<TableSchema, 'round' | 'winner'>>({ 'matchId': 'challenge6', round: 1 }, {
    projection: { round: 1, winner: 1 },
  }).then(console.log);

  // Find a row using other filter operators on other indexed columns.
  await table.findOne({ score: { $gte: 15 } }).then(console.log);

  // (Not recommended) You can filter on a non-indexed column, but it is long-running and inefficient.
  // The Data API returns a warning, if you are listening for or logging warnings.
  await table.findOne({ score: { $gt: timestamp() } });

  // To get any row, pass an empty filter.
  await table.findOne({}).then(console.log);

  // Use sort to perform a vector search on a vector-indexed column.
  await table.findOne<{ winner: string }>({}, {
    sort: { mVector: vector([.2, .3, .4]) },
    projection: { winner: 1 }
  }).then(console.log);

  // includeSimilarity returns the similarity score for the vector search.
  // If you use a projection, you must include $similarity in the projection type.
  await table.findOne<{ winner: string, $similarity: number }>({}, {
    sort: { mVector: vector([.2, .3, .4]) },
    projection: { winner: 1 },
    includeSimilarity: true,
  }).then(console.log);

  // You can also use sort for regular ascending/descending sorts on a given column.
  await table.findOne({ matchId: 'round5' }, {
    sort: { round: -1 },
  }).then(console.log);

// find examples

  // Find rows with an exact match on one column, inherently using the equality ($eq) filter operator.
  // Lazily iterate over results to print output like:
  // - (R:1): winner Donna
  // - (R:2): winner Erick
  // - (R:3): winner Fiona
  for await (const row of table.find({ matchId: 'challenge6' })) {
    console.log(`(R:${row.round}): winner ${row.winner}`);
  }

  // Projections optimize bandwidth by returning specified columns instead of entire rows.
  // Specify the exact return type to prevent accidental type errors.
  type ProjectedSchema = Pick<TableSchema, 'round' | 'winner'>;

  for await (const row of table.find({ matchId: 'challenge6' }).project<ProjectedSchema>({ round: 1, winner: 1 })) {
    console.log(`(R:${row.round}): winner ${row.winner}`);
  }

  // Another example of the implied equality operator.
  await table.find({ matchId: 'challenge6' }).toArray().then(console.log);

  // Find rows using other filter operators on other indexed columns.
  // You can filter on non-indexed columns, but this is long-running and inefficient.
  await table.find({ score: { $gte: 15 } }).toArray().then(console.log);

  // (Not recommended; long-running and inefficient) To get all rows, pass an empty filter.
  await table.find({}).toArray().then(console.log);

  // Use sort to perform a vector search on a vector-indexed column.
  await table.find({})
    .sort({ mVector: vector([.2, .3, .4]) })
    .project<{ winner: number }>({ winner: 1 })
    .toArray()
    .then(console.log);

  // Use sort and filter together for a hybrid search.
  // This example also use includeSimilarity to return the similarity scores of the vector search.
  // If you use a projection, you must include $similarity in the projection type.
  await table.find({ matchId: 'fight4' })
    .sort({ mVector: vector([.2, .3, .4]) })
    .includeSimilarity(true)
    .project<{ winner: number, $similarity: number }>({ winner: 1 })
    .toArray()
    .then(console.log);

  // Use sort for regular ascending/descending sorts on a given column.
  await table.find({ matchId: 'round5' })
    .sort({ round: -1 })
    .toArray()
    .then(console.log);

  // You can also use mapping.
  await table.find({ matchId: 'fight5' })
    .sort({ round: -1 })
    .limit(5)
    .map(row => row.winner.toUpperCase())
    .toArray()
    .then(console.log);

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

"data": {
  "document": {
    "name": "Tal Noor",
    "email": "tal@example.com",
    "graduated": true,
    "graduation_year": 2014
  }
}