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!