Projections for tables
In findOne and find, you can use the projection parameter to select specific columns to include or exclude from the response.
The projection can’t mix inclusions and exclusions.
If projection is empty or unspecified, the Data API applies the default projection.
For rows, the default projection includes all columns.
Over HTTP, the Data API always excludes null values in responses, regardless of projection
For example, if find matches multiple rows, the response can include different columns for each row, depending on which columns are null in each row.
You cannot forcibly include null values in the response.
The Python, TypeScript and Java clients reconstruct returned rows by adding the columns omitted
in the API response.
Therefore, when using a client, rows from findOne and find operations always have
the same columns, as specified by the projection, regardless of the presence of null values.
|
In order to optimize the response size and improve performance, DataStax recommends always providing an explicit projection tailored to the needs of the application. |
Projection syntax
A projection is expressed as a mapping of column names to boolean values.
Use true mapping to include only the specified columns.
For example, the following true mapping returns the columns name and email:
{ "name": true, "email": true }Alternatively, use a false mapping to exclude the specified columns.
All other non-excluded columns are returned.
For example, the following false mapping returns all columns except name and email:
{ "name": false, "email": false }The values in a projection map can be objects, booleans, decimals, or integers, but the Data API ultimately evaluates all of these as booleans.
For example, the following projection evaluates to true (include) for all four columns:
{ "column1": true, "column2": 1, "column3": 90.0, "column4": { "keep": "yes!" } }Whereas this projection evaluates to false (exclude) for all four columns:
{ "column1": false, "column2": 0, "column3": 0.0, "column4": {} }Passing null-like types (such as {}, null or 0) for the whole projection mapping is equivalent to not specifying a projection, causing the Data API to use the default projection.
A projection can’t mix include and exclude projections.
It can contain only true or only false values for the given columns.
For example, {"column1": true, "column2": false} is an invalid projection that results in an API error.
A projection can’t include or exclude sub-column values, such as keys in map columns.
To include all columns, you can use the wildcard projection { "*": true }.
This is equivalent to the default projection.
The Data API doesn’t support false wildcard projections for tables.
Projection examples by language
-
Python
-
TypeScript
-
Java
-
curl
For the Python client, the projection can be any of the following:
-
A dictionary (
dict[str, Any]) to include specific columns in the response, like{column_name: True}. -
A dictionary (
dict[str, Any]) to exclude specific columns from the response, like{column_name: False}. -
A list or other iterable over key names that are implied to be included in the projection.
# The following two statements are equivalent:
document = table.find_one(
{"user_id": 101},
projection={"name": True, "city": True},
)
document = table.find_one(
{"user_id": 101},
projection=["name", "city"],
)
# Return the whole rows (default):
table.find({}, projection={'*': True}).to_list()
# Exclude certain columns:
table.find({}, projection={'name': False}).to_list()For information about default projections and projection syntax, see the preceding explanation of projection clauses.
The TypeScript client takes in an untyped JS object for the projection parameter.
When specifying a projection, you must handle the return type carefully.
To avoid some false typing claims, when it cannot be inferred that a projection is not passed, Partial<Row> is used as the return type for find & findOne operations.
This is only Partial, not DeepPartial, so it only makes the top-level keys optional.
DataStax recommends that you provide your own explicit type override on find/findOne operations when using projections.
interface TableSchema {
matchId: string,
round: number,
score?: number | null,
winner?: string | null,
}
// row1 is of type TableSchema
const row1 = await table.findOne({});
// row2 is of type Partial<TableSchema>
const row2 = await table.findOne({}, {
projection: { score: 1, winner: 1 }
});
// row3 is of type { score?: string | null, winner?: number | null }
const row3 = await table.findOne<Pick<TableSchema, 'score' | 'winner'>>({}, {
projection: { score: 1, winner: 1 }
});
// Projections also work with cursors
// rows are of type { score: number, winner: string, $similarity: number }
const cursor = await table.find({})
.project<{ score: string, winner: number }>({ score: 1, winner: 1 })
.includeSimilarity();For information about default projections and projection syntax, see the preceding explanation of projection clauses.
If you perform a vector search that includes projection and includeSimilarity: true, then you must manually include $similarity in the type of the returned rows.
For example:
await table.findOne<{ winner: string, $similarity: number }>({}, {
sort: { mVector: vector([.2, .3, .4]) },
projection: { winner: 1 },
includeSimilarity: true,
}).then(console.log);If you don’t include projection, then $similarity is inferred to be a part of the returned rows.
To support the projection mechanism, the Java client has different Options classes that provide the projection method in the helpers.
This method takes an array of Projection classes with the column name and a boolean flag indicating inclusion or exclusion:
Projection p1 = new Projection("column1", true);
Projection p2 = new Projection("column2", true);
TableFindOneOptions options1 = new TableFindOneOptions().projection(p1, p2);To simplify this syntax, you can use the Projections syntactic sugar:
TableFindOneOptions options2 = new TableFindOneOptions()
.projection(Projections.include("column1", "column2"));
TableFindOneOptions options3 =new TableFindOneOptions()
.projection(Projections.exclude("column1", "column2"));For information about default projections and projection syntax, see the preceding explanation of projection clauses.
projection is an optional parameter on find and findOne.
The following example returns the first 100 rows matching the given query, after skipping the first 20 matches.
The response contains only the name and email value for each returned row, if they are not null.
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 '{
"find": {
"filter": { "email": "tal@example.com" },
"projection": { "name": true, "email": true },
"options": {
"skip": 20,
"limit": 100
}
}
}' | jqFor information about default projections and projection syntax, see the preceding explanation of projection clauses.
