Projections for tables
You can use a projection with some Data API commands to control what columns to return.
When you specify a projection, you specify which columns to include or exclude. You cannot specify a mix of inclusions and exclusions.
If you don’t specify a projection, the Data API returns all columns. In order to optimize the response size and improve read performance, DataStax recommends always providing a projection tailored to the needs of your application.
Null values
If you make a direct HTTP request to the Data API, the response always excludes null
values, regardless of the projection.
This means that the response may include different columns for each returned row, depending on which columns are null
in the row.
You cannot forcibly include null
values in the response.
If you use one of the clients, the client adds columns that were omitted due to a null
value.
Include specific columns
The following examples include the is_checked_out
and title
columns.
-
Python
-
TypeScript
-
Java
-
curl
You can use a dictionary or an iterable of columns names to include.
Example using a dictionary:
from astrapy import DataAPIClient
# Get an existing collection
client = DataAPIClient()
database = client.get_database(
"API_ENDPOINT",
token="APPLICATION_TOKEN",
)
table = database.get_table("TABLE_NAME")
# Use a projection
result = table.find_one(
{"number_of_pages": {"$lt": 300}},
projection={"is_checked_out": True, "title": True},
)
print(result)
Example using an iterable:
from astrapy import DataAPIClient
# Get an existing collection
client = DataAPIClient()
database = client.get_database(
"API_ENDPOINT",
token="APPLICATION_TOKEN",
)
table = database.get_table("TABLE_NAME")
# Use a projection
result = table.find_one(
{"number_of_pages": {"$lt": 300}},
projection=["is_checked_out", "title"],
)
print(result)
The TypeScript client accepts an untyped Plain Old JavaScript Object (POJO) for the projection
parameter.
You can use any truthy value in the projection.
This example uses true
.
Consider type-casting to help you handle the return type.
By default, the response is typed as Partial<Row>
.
This is only Partial
, not DeepPartial
, so it only makes the top-level keys optional.
If you use a projection and you set the includeSimlarity
parameter to true
for a vector search, you must manually include $similarity
in the type of the returned rows.
-
Typed
-
Untyped
import { DataAPIClient } from "@datastax/astra-db-ts";
// Manually define the table schema
interface ExampleSchema {
title: string;
author: string;
is_checked_out?: boolean;
}
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table<ExampleSchema>("TABLE_NAME");
// Use a projection
(async function () {
const result = await table.findOne<
Pick<ExampleSchema, "is_checked_out" | "title">
>(
{ number_of_pages: { $lt: 300 } },
{ projection: { is_checked_out: true, title: true } },
);
console.log(result);
})();
import { DataAPIClient } from "@datastax/astra-db-ts";
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table("TABLE_NAME");
// Use a projection
(async function () {
const result = await table.findOne(
{ number_of_pages: { $lt: 300 } },
{ projection: { is_checked_out: true, title: true } },
);
console.log(result);
})();
Projections also work on cursors:
-
Typed
-
Untyped
import { DataAPIClient } from "@datastax/astra-db-ts";
// Manually define the table schema
interface ExampleSchema {
title: string;
author: string;
is_checked_out?: boolean;
}
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table<ExampleSchema>("TABLE_NAME");
// Use a projection
(async function () {
const result = table
.find({ number_of_pages: { $lt: 300 } })
.limit(3)
.project<Pick<ExampleSchema, "is_checked_out" | "title">>({
is_checked_out: true,
title: true,
});
for await (const row of result) {
console.log(row);
}
})();
import { DataAPIClient } from "@datastax/astra-db-ts";
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table("TABLE_NAME");
// Use a projection
(async function () {
const result = table
.find({ number_of_pages: { $lt: 300 } })
.limit(3)
.project({ is_checked_out: true, title: true });
for await (const row of result) {
console.log(row);
}
})();
Java client has different Options
classes that provide a projection
method.
The projection
method takes either an array of Projection
classes with the column name and a boolean flag indicating inclusion or exclusion, or Projection
syntactic sugar indicating which column to include or exclude.
Example using the Projection
syntactic sugar:
import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.core.query.Filters;
import com.datastax.astra.client.core.query.Projection;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.commands.options.TableFindOneOptions;
import com.datastax.astra.client.tables.definition.rows.Row;
import java.util.Optional;
public class Example {
public static void main(String[] args) {
// Get an existing table
Table<Row> table =
new DataAPIClient("APPLICATION_TOKEN")
.getDatabase("API_ENDPOINT")
.getTable("TABLE_NAME");
// Use a projection
Filter filter = Filters.lt("number_of_pages", 300);
TableFindOneOptions options =
new TableFindOneOptions().projection(Projection.include("is_checked_out", "title"));
Optional<Row> result = table.findOne(filter, options);
System.out.println(result);
}
}
Example using an array of Projection
classes:
import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.core.query.Filters;
import com.datastax.astra.client.core.query.Projection;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.commands.options.TableFindOneOptions;
import com.datastax.astra.client.tables.definition.rows.Row;
import java.util.Optional;
public class Example {
public static void main(String[] args) {
// Get an existing table
Table<Row> table =
new DataAPIClient("APPLICATION_TOKEN")
.getDatabase("API_ENDPOINT")
.getTable("TABLE_NAME");
// Use a projection
Filter filter = Filters.lt("number_of_pages", 300);
TableFindOneOptions options =
new TableFindOneOptions()
.projection(new Projection("is_checked_out", true), new Projection("title", true));
Optional<Row> result = table.findOne(filter, options);
System.out.println(result);
}
}
You can use any truthy value in the projection.
This example uses true
.
curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
--header "Token: APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
"findOne": {
"filter": {"number_of_pages": {"$lt": 300}},
"projection": {"is_checked_out": true, "title": true}
}
}'
Exclude specific columns
The following examples exclude the is_checked_out
and title
columns.
-
Python
-
TypeScript
-
Java
-
curl
Use a dictionary of column names to exclude.
from astrapy import DataAPIClient
# Get an existing collection
client = DataAPIClient()
database = client.get_database(
"API_ENDPOINT",
token="APPLICATION_TOKEN",
)
table = database.get_table("TABLE_NAME")
# Use a projection
result = table.find_one(
{"number_of_pages": {"$lt": 300}},
projection={"is_checked_out": False, "title": False},
)
print(result)
The TypeScript client accepts an untyped Plain Old JavaScript Object (POJO) for the projection
parameter.
You can use any falsy value in the projection.
This example uses false
.
Consider type-casting to help you handle the return type.
By default, the response is typed as Partial<Row>
.
This is only Partial
, not DeepPartial
, so it only makes the top-level keys optional.
If you use a projection and you set the includeSimlarity
parameter to true
for a vector search, you must manually include $similarity
in the type of the returned rows.
-
Typed
-
Untyped
import { DataAPIClient } from "@datastax/astra-db-ts";
// Manually define the table schema
interface ExampleSchema {
title: string;
author: string;
is_checked_out?: boolean;
}
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table<ExampleSchema>("TABLE_NAME");
// Use a projection
(async function () {
const result = await table.findOne<
Omit<ExampleSchema, "is_checked_out" | "title">
>(
{ number_of_pages: { $lt: 300 } },
{ projection: { is_checked_out: false, title: false } },
);
console.log(result);
})();
import { DataAPIClient } from "@datastax/astra-db-ts";
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table("TABLE_NAME");
// Use a projection
(async function () {
const result = await table.findOne(
{ number_of_pages: { $lt: 300 } },
{ projection: { is_checked_out: false, title: false } },
);
console.log(result);
})();
Projections also work on cursors:
-
Typed
-
Untyped
import { DataAPIClient } from "@datastax/astra-db-ts";
// Manually define the table schema
interface ExampleSchema {
title: string;
author: string;
is_checked_out?: boolean;
}
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table<ExampleSchema>("TABLE_NAME");
// Use a projection
(async function () {
const result = table
.find({ number_of_pages: { $lt: 300 } })
.limit(3)
.project<Omit<ExampleSchema, "is_checked_out" | "title">>({
is_checked_out: false,
title: false,
});
for await (const row of result) {
console.log(row);
}
})();
import { DataAPIClient } from "@datastax/astra-db-ts";
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table("TABLE_NAME");
// Use a projection
(async function () {
const result = table
.find({ number_of_pages: { $lt: 300 } })
.limit(3)
.project({ is_checked_out: false, title: false });
for await (const row of result) {
console.log(row);
}
})();
Java client has different Options
classes that provide a projection
method.
The projection
method takes either an array of Projection
classes with the column name and a boolean flag indicating inclusion or exclusion, or Projection
syntactic sugar indicating which columns to include or exclude.
Example using the Projection
syntactic sugar:
import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.core.query.Filters;
import com.datastax.astra.client.core.query.Projection;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.commands.options.TableFindOneOptions;
import com.datastax.astra.client.tables.definition.rows.Row;
import java.util.Optional;
public class Example {
public static void main(String[] args) {
// Get an existing table
Table<Row> table =
new DataAPIClient("APPLICATION_TOKEN")
.getDatabase("API_ENDPOINT")
.getTable("TABLE_NAME");
// Use a projection
Filter filter = Filters.lt("number_of_pages", 300);
TableFindOneOptions options =
new TableFindOneOptions().projection(Projection.exclude("is_checked_out", "title"));
Optional<Row> result = table.findOne(filter, options);
System.out.println(result);
}
}
Example using an array of Projection
classes:
import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.core.query.Filters;
import com.datastax.astra.client.core.query.Projection;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.commands.options.TableFindOneOptions;
import com.datastax.astra.client.tables.definition.rows.Row;
import java.util.Optional;
public class Example {
public static void main(String[] args) {
// Get an existing table
Table<Row> table =
new DataAPIClient("APPLICATION_TOKEN")
.getDatabase("API_ENDPOINT")
.getTable("TABLE_NAME");
// Use a projection
Filter filter = Filters.lt("number_of_pages", 300);
TableFindOneOptions options =
new TableFindOneOptions()
.projection(new Projection("is_checked_out", false), new Projection("title", false));
Optional<Row> result = table.findOne(filter, options);
System.out.println(result);
}
}
You can use any falsy value in the projection.
This example uses false
.
curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
--header "Token: APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
"findOne": {
"filter": {"number_of_pages": {"$lt": 300}},
"projection": {"is_checked_out": false, "title": false}
}
}'
Include all columns
The wildcard projection "*"
represents the whole row.
If you use this projection, it must be the only key in the projection.
If set to true, all columns are returned. This is equivalent to not specifying a projection.
You cannot set the wildcard projection to false.
-
Python
-
TypeScript
-
Java
-
curl
from astrapy import DataAPIClient
# Get an existing collection
client = DataAPIClient()
database = client.get_database(
"API_ENDPOINT",
token="APPLICATION_TOKEN",
)
table = database.get_table("TABLE_NAME")
# Use a projection
result = table.find_one(
{"number_of_pages": {"$lt": 300}},
projection={"*": True},
)
print(result)
import { DataAPIClient } from "@datastax/astra-db-ts";
// Get an existing table
const client = new DataAPIClient("APPLICATION_TOKEN");
const database = client.db("API_ENDPOINT");
const table = database.table("TABLE_NAME");
// Use a projection
(async function () {
const result = await table.findOne(
{ number_of_pages: { $lt: 300 } },
{ projection: { "*": true } },
);
console.log(result);
})();
import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.core.query.Filters;
import com.datastax.astra.client.core.query.Projection;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.commands.options.TableFindOneOptions;
import com.datastax.astra.client.tables.definition.rows.Row;
import java.util.Optional;
public class Example {
public static void main(String[] args) {
// Get an existing table
Table<Row> table =
new DataAPIClient("APPLICATION_TOKEN")
.getDatabase("API_ENDPOINT")
.getTable("TABLE_NAME");
// Use a projection
Filter filter = Filters.lt("number_of_pages", 300);
TableFindOneOptions options = new TableFindOneOptions().projection(new Projection("*", true));
Optional<Row> result = table.findOne(filter, options);
System.out.println(result);
}
}
curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
--header "Token: APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
"findOne": {
"filter": {"number_of_pages": {"$lt": 300}},
"projection": {"*": true}
}
}'
Unsupported cases
A projection cannot include or exclude sub-column values, such as keys in map
columns.
The Data API doesn’t support false wildcard projections for tables.