How to Filter AutoREST Results with the q Parameter

An AutoREST collection returns all rows by default. To get only some of them, clients pass a filter in the q query parameter, written as JSON: equality, comparisons, LIKE patterns, lists, AND and OR, and sorting. ORDS turns the filter into a WHERE clause, so the database does the work. This guide filters a REST-enabled view of airports and shows what happens with operators ORDS does not know.

Before You Start

You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.

ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.

Syntax

GET .../object_alias/?q={"column":"value"}
GET .../object_alias/?q={"column":{"$gt":100}}
GET .../object_alias/?q={"$or":[{...},{...}],"$orderby":{"column":"desc"}}

Operators include $eq, $ne, $lt, $lte, $gt, $gte, $like, $instr, $in, $between, $null, $notnull, $and, $or, and $orderby. Encode the JSON in the URL; curl does it with -G and --data-urlencode.

A View to Filter

AIRPORTS_V shows five columns of AIRPORTS and is read-only, so the REST API can never change the table.

Example:

create or replace view airports_v as
select airport_code, city, country_code, elevation_ft, is_hub
from   airports
with read only;

begin
  ords.enable_object(p_enabled        => true,
                     p_schema         => 'NIMBUS',
                     p_object         => 'AIRPORTS_V',
                     p_object_type    => 'VIEW',
                     p_object_alias   => 'airports',
                     p_auto_rest_auth => false);
  commit;
end;
/

Output:

View AIRPORTS_V created.

PL/SQL procedure successfully completed.

Filter by Equality

Example:

curl -k -G https://localhost:8443/ords/nimbus/airports/ \
  --data-urlencode 'q={"country_code":"IN"}'

Output:

{
    "items": [
        {
            "airport_code": "BOM",
            "city": "Mumbai",
            "country_code": "IN",
            "elevation_ft": 39,
            "is_hub": false
        },
        {
            "airport_code": "DEL",
            "city": "Delhi",
            "country_code": "IN",
            "elevation_ft": 777,
            "is_hub": false
        },
        {
            "airport_code": "BLR",
            "city": "Bengaluru",
            "country_code": "IN",
            "elevation_ft": 3002,
            "is_hub": false
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 3,
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22country_code%22:%22IN%22%7D"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/nimbus/metadata-catalog/airports/"
        },
        {
            "rel": "first",
            "href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22country_code%22:%22IN%22%7D"
        }
    ]
}

The three Indian airports come back, each with its own link, and count is 3.

More Filters

Each request below ran against the same view; the table shows the airports it returned.

Combine Conditions and Sort

$or takes a list of conditions, and $orderby sorts the result. This request asks for the Japanese airport or any airport at 5,000 feet or more, highest first.

Example:

curl -k -G https://localhost:8443/ords/nimbus/airports/ \
  --data-urlencode 'q={"$or":[{"country_code":"JP"},{"elevation_ft":{"$gte":5000}}],"$orderby":{"elevation_ft":"desc"}}'

Output:

{
    "items": [
        {
            "airport_code": "JNB",
            "city": "Johannesburg",
            "country_code": "ZA",
            "elevation_ft": 5558,
            "is_hub": false
        },
        {
            "airport_code": "NBO",
            "city": "Nairobi",
            "country_code": "KE",
            "elevation_ft": 5327,
            "is_hub": false
        },
        {
            "airport_code": "NRT",
            "city": "Tokyo",
            "country_code": "JP",
            "elevation_ft": 141,
            "is_hub": false
        }
    ],
    "hasMore": false,
    "limit": 25,
    "offset": 0,
    "count": 3,
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22%24or%22:%5B%7B%22country_code%22:%22JP%22%7D%2C%7B%22elevation_ft%22:%7B%22%24gte%22:5000%7D%7D%5D%2C%22%24orderby%22:%7B%22elevation_ft%22:%22desc%22%7D%7D"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/nimbus/metadata-catalog/airports/"
        },
        {
            "rel": "first",
            "href": "https://localhost:8443/ords/nimbus/airports/?q=%7B%22%24or%22:%5B%7B%22country_code%22:%22JP%22%7D%2C%7B%22elevation_ft%22:%7B%22%24gte%22:5000%7D%7D%5D%2C%22%24orderby%22:%7B%22elevation_ft%22:%22desc%22%7D%7D"
        }
    ]
}

Johannesburg and Nairobi come first by elevation, then Tokyo Narita.

When a Filter Is Wrong

$like compares case-sensitively: {"city":{"$like":"s%"}} with a lowercase s returns no rows. There is no case-insensitive $ilike operator; using one is rejected:

Output (HTTP 400 Bad Request):

{
    "code": "BadRequest",
    "message": "Bad Request",
    "type": "tag:oracle.com,2020:error/BadRequest",
    "instance": "tag:oracle.com,2020:ecid/RWWVuix6bn_4SmbxLUgcFA"
}

A misspelled column, such as elevationft, is rejected with 403 Forbidden and a message about a function that is not accessible:

Output (HTTP 403 Forbidden):

{
    "code": "Forbidden",
    "title": "Forbidden",
    "message": "The request could not be processed because a function referenced by the SQL statement being evaluated is not accessible or does not exist",
    "type": "tag:oracle.com,2020:error/Forbidden",
    "instance": "tag:oracle.com,2020:ecid/QYew-PgR1xpj2hqvVCBBpg"
}

Things to Know

• Filters apply only to columns of the enabled object; to filter on a calculated value, add it as a column of a view.
• Use $instr for "contains" searches, and store or expose values in one case when case-insensitive matching matters.
• Filters and paging work together: limit and offset apply to the filtered rows.

Related Guides

• How to REST-Enable a Table with ORDS AutoREST
• How to REST-Enable a Schema with ORDS.ENABLE_SCHEMA

Conclusion

The q parameter filters AutoREST collections with a JSON query: comparisons, patterns, lists, AND and OR, and sorting, all run as SQL in the database. Encode it in the URL, remember that $like is case-sensitive, and expose a view when clients need different columns to filter on.