14.5.3 Filtering/Paging Collection Query Endpoints

Use query string parameters to filter and page through Collection Query results.

ORDS Collection Query GET handlers support query filtering using the q query string parameter, whose value is an ORDS JSON filter predicate. For example, testing a GET request with the following URL finds action items whose name contains the word Party:

$ curl https://example.com/⋯/v0/actionitems?q={"name":{"$like":"%Party%"}}

The response looks like the following if just one action item in the system matches.

{
    "items": [
        {
            "_id": 14,
            "name": "End of Year Party",
            "status": "OPEN",
            "team": [
                {
                    "team_member_id": 23,
                    "name": "David",
                    "user_id": 4,
                    "role": "MEMBER"
                },
                {
                    "team_member_id": 24,
                    "name": "Georgia",
                    "user_id": 5,
                    "role": "LEAD"
                },
                {
                    "team_member_id": 25,
                    "name": "Jane",
                    "user_id": 13,
                    "role": "MEMBER"
                }
            ]
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 1,
    "links": [
      ⋰
    ]
}

Notice the following properties in the JSON response:

{
       ⋮
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 1,
       ⋮ 
}

An ORDS Collection Query handler's response includes the following top-level properties:

• count – rows in this response "pageful"
• limit – maximum rows in a single response, also called the "page size"
• offset – rows that were skipped before returning the current "page"
• hasMore – true if there are more rows to retrieve, false otherwise

ORDS Collection Query GET handlers support paging through data by adding the query string parameters:

• limit – maximum rows in a single response, also called the "page size"
• offset – rows that were skipped before returning the current "page"

The default value of offset is 0 if not specified, so to fetch the first 10 actions items, use:

$ curl https://example.com/⋯/v0/actionitems?limit=10

If the response includes a hasMore value of true, then get the second page of 10 action items with:

$ curl https://example.com/⋯/v0/actionitems?offset=10&limit=10

Tip:

When configuring Collection Query GET handlers, always include an ORDER BY clause in your query to ensure a predictable result when paging.

You can combine filtering with paging, too. For example, to retrieve the first 5 action items whose name starts with Feature:, use:

$ curl https://⋯/v0/actionitems?limit=5&q={"name":{"$like":"Feature:%"}}

If the response includes a hasMore value of true, then get the second page of 5 action items with:

$ curl https://⋯/v0/actionitems?offset=5&limit=5&q={"name":{"$like":"Feature:%"}}