Find rows (HTTP)

For details about additional languages, see:

Finds rows in a table using filter and sort clauses, including vector search.

For general information about working with tables and rows, see About tables with the Data API (HTTP).

Ready to write code? See the examples for this method to get started.

Result

The response includes a data.documents property, which is an array of objects representing rows that match the specified filter and sort clauses.

The columns included in the returned rows depend on the subset of columns that were requested in the projection. If requested and applicable, each row will also include a $similarity key with a numeric similarity score that represents the closeness of the sort vector and the row’s vector.

If the query supports pagination, the response also includes a data.nextPageState property, which indicates the ID of the next page of results, if any. For non-vector searches, the results will be paginated if more than 20 rows match the specified filter and sort clauses. For details about iteration, see Iterate over found rows.

For vector search, returns a single page of up to 1000 rows (or a lower amount if specified).

Some operations do not support pagination. These include:

Operations that require in-memory sort, such as allow filtering on non-indexed columns. The Data API returns a warning if this happens.
Vector searches.
Certain combinations of sort and filter options.

Example response:

{
  "data": {
    "documents":[
      {
        "_id":"85a54382-9227-4075-a543-829227407556",
        "title":"Within Silence of the Past",
        "is_checked_out":false
      },
      {
        "_id":"aa762475-4fc1-4477-b624-754fc1f477c7",
        "title":"Beyond Dreams and Forgotten Worlds",
        "is_checked_out":false
      }
    ],
    "nextPageState":"LQAAAAEBAAAAJGQ2OTk5NzY2LTgyODQtNDc3Mi05OTk3LTY2ODI4NGU3NzJjYQDwf///6wA="
  }
}

Example response if no rows were found:

{
  "data": {
    "documents": [],
    "nextPageState": null
  }
}

Parameters

Use the find command.

Command signature

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
--header "Token: APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "find": {
    "filter": FILTER,
    "sort": SORT,
    "projection": PROJECTION,
    "options": {
      "includeSimilarity": BOOLEAN,
      "skip": INTEGER,
      "limit": INTEGER
    }
  }
}'

For best performance, filter and sort on indexed columns, partition keys, and clustering keys.

Filtering on non-indexed columns is inefficient and resource-intensive, especially for large datasets. With the Data API clients, such operations can hit the client timeout limit before the underlying HTTP operation is complete. If you filter on non-indexed columns, the Data API will give a warning.

An empty filter or omitted filter may also result in an inefficient and long-running operation.

Additionally, the Data API can perform in-memory sorting, depending on the columns you sort on, the table’s partitioning structure, and whether the sorted columns are indexed. In-memory sorts can have performance implications.

Name Type Summary

Name	Type	Summary
`filter`	`object`	Optional. An object that defines filter criteria using the Data API filter syntax. The method only finds rows that match the filter criteria. Filters can improve performance by reducing the number of rows that the Data API processes. For a list of available filter operators and more examples, see Filter operators for tables (HTTP). To perform a vector search, use `sort` instead of `filter`. To avoid fetching unnecessary rows, which can contain tombstones, DataStax recommends that you use a filter that limits the number of rows scanned. For example, filter on partition key columns or indexed columns. Default: No filter For an example, see Use filters to find rows.
`sort`	`object`	Optional. Sorts rows by one or more columns, or performs a vector search. For more information, see Sort clauses for tables (HTTP). For examples, see Use sorting to find rows, Use vector search with a search vector to find rows, and Use vector search with a search string to find rows.
`projection`	`object`	Optional. Controls which columns are included or excluded in the returned rows. For more information, see Projections for tables (HTTP). DataStax recommends a projection to avoid unnecessarily returning large columns, such as `vector` columns with highly dimensional embeddings. Default: All columns For examples, see Include only specific columns in the response and Exclude specific columns from the response.
`skip`	`integer`	Optional. The number of rows to bypass (skip) before returning rows. The API excludes the first `n` rows matching the query, and the results begin at the `n+1` row. This parameter only applies if you also explicitly specify an ascending or descending sort criterion. This parameter is not valid with vector search.
`limit`	`integer`	Optional. Limit the total number of rows returned. Pagination can occur if more that 20 rows are found. Once the `limit` is reached, either in a single response or the last page of a paginated response, nothing more is returned. For vector search, a lower limit reduces the accuracy of the search and the time required for the search.
`options.includeSimilarity`	`boolean`	Optional. Whether to include a `$similarity` property in the response. The `$similarity` value represents the closeness of the sort vector and the row’s vector. This parameter doesn’t work with vectorize; it only works if you provide the search vector for vector search directly. Default: false For an example, see Include the similarity score with the result.

filter

object

Optional. An object that defines filter criteria using the Data API filter syntax. The method only finds rows that match the filter criteria. Filters can improve performance by reducing the number of rows that the Data API processes.

For a list of available filter operators and more examples, see Filter operators for tables (HTTP).

To perform a vector search, use sort instead of filter.

To avoid fetching unnecessary rows, which can contain tombstones, DataStax recommends that you use a filter that limits the number of rows scanned. For example, filter on partition key columns or indexed columns.

Default: No filter

For an example, see Use filters to find rows.

sort

object

Optional. Sorts rows by one or more columns, or performs a vector search.

For more information, see Sort clauses for tables (HTTP).

For examples, see Use sorting to find rows, Use vector search with a search vector to find rows, and Use vector search with a search string to find rows.

projection

object

Optional. Controls which columns are included or excluded in the returned rows.

For more information, see Projections for tables (HTTP).

DataStax recommends a projection to avoid unnecessarily returning large columns, such as vector columns with highly dimensional embeddings.

Default: All columns

For examples, see Include only specific columns in the response and Exclude specific columns from the response.

skip

integer

Optional. The number of rows to bypass (skip) before returning rows.

The API excludes the first n rows matching the query, and the results begin at the n+1 row.

This parameter only applies if you also explicitly specify an ascending or descending sort criterion. This parameter is not valid with vector search.

limit

integer

Optional. Limit the total number of rows returned.

Pagination can occur if more that 20 rows are found.

Once the limit is reached, either in a single response or the last page of a paginated response, nothing more is returned.

For vector search, a lower limit reduces the accuracy of the search and the time required for the search.

options.includeSimilarity

boolean

Optional. Whether to include a $similarity property in the response. The $similarity value represents the closeness of the sort vector and the row’s vector.

This parameter doesn’t work with vectorize; it only works if you provide the search vector for vector search directly.

Default: false

For an example, see Include the similarity score with the result.

Examples

The following examples demonstrate how to find rows in a table.

Use filters to find rows

You can use a filter to find rows that match specific criteria. For example, you can find rows with an is_checked_out value of false and a number_of_pages value less than 300.

For optimal performance, you only filter on indexed columns. The Data API returns a warning if you filter on a non-indexed column.

For a list of available filter operators, see Filter operators for tables (HTTP).

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {"$and": [
      {"is_checked_out": false},
      {"number_of_pages": {"$lt": 300}}
    ]}
  }
}'

Use vector search with a search vector to find rows

Perform a vector search by providing a search vector in the sort clause. This returns the row whose vector column value is most similar to the provided search vector.

The vector column must be indexed.

If your table has multiple vector columns, you can only sort on one vector column at a time.

You can provide the search vector as an array of floats, or you can use $binary to provide the search vector as a Base64-encoded string. $binary can be more performant.

For more information about how to convert an array of floats to a Base64-encoded string, see BLOB type (HTTP).

Array of floats
$binary

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "sort": { "summary_genres_vector": [0.08, -0.62, 0.39] }
  }
}'

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "sort": { "summary_genres_vector": {"$binary": "PaPXCr8euFI+x64U"} }
  }
}'

Use vector search with a search string to find rows

Perform a vector search by providing a search string in the sort clause. The search string is converted to a search vector, and the row whose vector column value is most similar to the search vector is returned.

The vector column must have an embedding provider integration. You can configure embedding provider integrations when you create a table, add a vector column to an existing table, or alter an existing vector column. The vector column must be indexed.

If your table has multiple vector columns, you can only sort on one vector column at a time.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "sort": { "summary_genres_vector": "Text to vectorize" }
  }
}'

Use lexicographical matching to find rows

Lexicographical matching 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.

There are two ways to use lexicographical matching to find rows with the Data API:

Sort to find rows with a text or ascii column value that is most relevant to a given string of space-separated keywords or terms.
Filter with the $match operator to find rows with a text or ascii column value that is a lexicographical match to the specified string of space-separated keywords or terms

You can use these strategies together or separately.

Lexicographical matching is only available for text or ascii columns that have a text index, not a regular index. For more information, see Create a text index (HTTP) and Indexes in tables (HTTP).

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {
      "summary": {"$match": "futuristic laboratory discovery"}
    },
    "sort": { 
      "summary": "futuristic laboratory"
    }
  }
}'

Use sorting to find rows

You can use a sort clause to sort rows by one or more columns.

For best performance, only sort on columns that are indexed or that are part of the primary key.

For more information, see Sort clauses for tables (HTTP).

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": { "is_checked_out": false },
    "sort": {
      "rating": 1,
      "title": -1
    }
  }
}'

Use an empty filter to find all rows

To find all rows, use an empty filter.

Avoid this if you have a large number of rows.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {}
  }
}'

Include the similarity score with the result

If you use a vector search to find rows, you can also include a $similarity property in the result. The $similarity value represents the closeness of the sort vector and the value of the row’s vector column.

This parameter doesn’t work with vectorize; it only works if you provide the search vector for vector search directly.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "sort": { "summary_genres_vector": [0.08, -0.62, 0.39] },
    "options": { "includeSimilarity": true }
  }
}'

Include only specific columns in the response

To specify which columns to include or exclude in the returned row, use a projection.

The following example demonstrates an inclusive projection.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {"number_of_pages": {"$lt": 300}},
    "projection": {"is_checked_out": true, "title": true}
  }
}'

Exclude specific columns from the response

To specify which columns to include or exclude in the returned row, use a projection.

The following example demonstrates an exclusive projection.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {"number_of_pages": {"$lt": 300}},
    "projection": {"is_checked_out": false, "title": false}
  }
}'

Limit the number of rows returned

Specify a limit to only fetch up to a certain number of rows.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {"$and": [
      {"is_checked_out": false},
      {"number_of_pages": {"$lt": 300}}
    ]},
    "options": {
      "limit": 3
    }
  }
}'

Skip rows

You can specify a number of rows to skip (bypass) before returning rows.

You can only do this if your find explicitly includes an ascending or descending sort criterion. You cannot do this in conjunction with vector search.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": { "is_checked_out": false },
    "sort": {
      "rating": 1,
      "title": -1
    },
    "options": {
      "skip": 5
    }
  }
}'

Use filter, sort, and projection together

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {"$and": [
      {"is_checked_out": false},
      {"number_of_pages": {"$lt": 300}}
    ]},
    "sort": {
      "rating": 1,
      "title": -1
    },
    "projection": {"is_checked_out": true, "title": true}
  }
}'

Iterate over found rows

If the response includes a non-null nextPageState, then the specified sort or filter operation supports pagination, and more rows than the ones already returned exist.

To fetch additional rows, you must send a request with the nextPageState value from your previous request. For example:

Send an initial request

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {"is_checked_out": false}
  }
}'

Get the data.documents.nextPageState value from the response

{
  "data": {
    "documents": [
      {
        "_id": { "$uuid": "018e65c9-df45-7913-89f8-175f28bd7f74" }
      },
      {
        "_id": { "$uuid": "018e65c9-e33d-749b-9386-e848739582f0" }
      }
    ],
    "nextPageState": "NEXT_PAGE_STATE"
  }
}

Use the data.documents.nextPageState from the previous response to request the next page of results.

curl -sS -L -X POST "API_ENDPOINT/api/json/v1/KEYSPACE_NAME/TABLE_NAME" \
  --header "Token: APPLICATION_TOKEN" \
  --header "Content-Type: application/json" \
  --data '{
  "find": {
    "filter": {"is_checked_out": false},
    "options": {
      "pageState": "NEXT_PAGE_STATE_FROM_PRIOR_RESPONSE"
    }
  }
}'

Once nextPageState is null, you have fetched all matching rows.

{
  "data": {
    "documents": [
      {
        "_id": { "$uuid": "018e65c9-df45-7913-89f8-175f28bd7f74" }
      },
      {
        "_id": { "$uuid": "018e65c9-e33d-749b-9386-e848739582f0" }
      }
    ],
    "nextPageState": null
  }
}

Find rows (HTTP)

Result

Parameters

Examples

Use filters to find rows

Use vector search with a search vector to find rows

Use vector search with a search string to find rows

Use lexicographical matching to find rows

Use sorting to find rows

Use an empty filter to find all rows

Include the similarity score with the result

Include only specific columns in the response

Exclude specific columns from the response

Limit the number of rows returned

Skip rows

Use filter, sort, and projection together

Iterate over found rows

Was this helpful?

Give Feedback