How do I filter with the REST API in Astra DB?

Question

Currently, I have problem when using Rest API in DataStax Astra.

For example data:

{
  "count": 2,
  "Members": [
    {
      "created_at": "",
      "intfield": null,
      "id": "294",
      "role": "BA",
      "username": "Join"
    },
    {
      "created_at": "",
      "intfield": null,
      "id": "180",
      "role": "Back",
      "username": "Kien Nguyen"
    },
    {
      "created_at": "",
      "intfield": null,
      "id": "180",
      "role": "Back",
      "username": "Kien Nguyen"
    }]
}

Now, I want to get all Member with role Back (backend). I try using 2 api same the above image API But not success. Maybe, I passed incorrect input or input body to 2 api. I searched the forums but couldn't find a solution.

Any answer is appreciated.

Thank a lot!

Answer 1

Your question is missing crucial details including:

1. the table schema, and
2. the REST API query.

In any case, the result set you posted appears to have been "doctored" because it reports "count": 2 but there are 3 records in the list, with "id": "180" duplicated.

So instead, I'm going to use an example to illustrate how to filter on a field. Here is the table I've created in my DB:

CREATE TABLE community.members (
    id int PRIMARY KEY,
    name text,
    role text
)

It is populated with the following sample data:

 id  | name   | role
-----+--------+----------
 123 |  alice |  backend
 456 |    bob |  backend
 789 | charli | frontend

To filter based on id = 123, we will use the following:

• endpoint - /api/rest/v2/keyspaces/community/members
• filter - where={"id": {"$eq":123}}

The resulting request URL for REST API call (with URL-escaped characters) is:

https://*.apps.astra.datastax.com/api/rest/v2/keyspaces/community/members?where=%7B%22id%22%3A%20%7B%22%24eq%22%3A123%7D%7D

and the response is:

{
  "count": 1,
  "data": [
    {
      "name": "alice",
      "id": 123,
      "role": "backend"
    }
  ]
}

If I try to filter with role = 'backend' (or where={"role": {"$eq":"backend"}}), this REST API call:

https://*.apps.astra.datastax.com/api/rest/v2/keyspaces/community/members?where=%7B%22role%22%3A%20%7B%22%24eq%22%3A%22backend%22%7D%7D

will return an error 400 because role is not the partition key so we can't filter on it:

{
  "description": "Bad request: org.apache.cassandra.stargate.exceptions.InvalidRequestException: \
    Cannot execute this query as it might involve data filtering and thus may have \
    unpredictable performance. If you want to execute this query despite the \
    performance unpredictability, use ALLOW FILTERING",
  "code": 400
}

The workaround is to index the role column so we can filter on it:

CREATE INDEX ON community.members (role);

Now we can query it with this REST API call:

https://*.apps.astra.datastax.com/api/rest/v2/keyspaces/community/members?where=%7B%22role%22%3A%20%7B%22%24eq%22%3A%22backend%22%7D%7D

to get:

{
  "count": 2,
  "data": [
    {
      "name": "alice",
      "id": 123,
      "role": "backend"
    },
    {
      "name": "bob",
      "id": 456,
      "role": "backend"
    }
  ]
}

For details and a few more examples, see Developing with the Astra DB REST API. Cheers!

Answer 2

If you're dealing directly with JSON type data/payload, you could leverage the Document API as opposed to the REST API.

Here is how one would achieve this with JSON/Document API,

curl -L -X  GET 'https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/namespaces/myworld/collections/members?where=\{"role":\{"$eq":"Back"\}\}' \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json'

where, I've assumed myworld as the namespace and members as the collection name.

In case if you update your original question with the actual table schema and want other directions using other APIs, I'll update my answer accordingly. Cheers.

Bonus: You can find the Postman collection resources here which contain examples from all available APIs and this resource will be kept updated with latest information all the time.