Record Collection Filtering

You can filter the collection of all record instances by using the q query parameter to specify filter conditions. Each condition consists of a field name, an operator, and a value. You can join several conditions using the AND / OR logical operators, and you can use () to mark precedence.

REST web services uses the N/query module to perform record collection filtering. Before filtering collections in REST web services, see N/query Module for information about how this module works.

The following table contains the list of available query operators with their associated field types.

Field Type	Allowed Filters
None	EMPTY, EMPTY_NOT
Boolean	IS, IS_NOT
Double, Integer, Float, Number, Duration	ANY_OF, ANY_OF_NOT, BETWEEN, BETWEEN_NOT, EQUAL, EQUAL_NOT, GREATER, GREATER_NOT, GREATER_OR_EQUAL, GREATER_OR_EQUAL_NOT, LESS, LESS_NOT, LESS_OR_EQUAL, LESS_OR_EQUAL_NOT, WITHIN, WITHIN_NOT
String	CONTAIN, CONTAIN_NOT, IS, IS_NOT, START_WITH, START_WITH_NOT, END_WITH, END_WITH_NOT
Date / Time	AFTER, AFTER_NOT, BEFORE, BEFORE_NOT, ON, ON_NOT, ON_OR_AFTER, ON_OR_AFTER_NOT, ON_OR_BEFORE, ON_OR_BEFORE_NOT

Field Type

Allowed Filters

None

EMPTY, EMPTY_NOT

Boolean

IS, IS_NOT

Double, Integer, Float, Number, Duration

ANY_OF, ANY_OF_NOT, BETWEEN, BETWEEN_NOT, EQUAL, EQUAL_NOT, GREATER, GREATER_NOT, GREATER_OR_EQUAL, GREATER_OR_EQUAL_NOT, LESS, LESS_NOT, LESS_OR_EQUAL, LESS_OR_EQUAL_NOT, WITHIN, WITHIN_NOT

String

CONTAIN, CONTAIN_NOT, IS, IS_NOT, START_WITH, START_WITH_NOT, END_WITH, END_WITH_NOT

Date / Time

AFTER, AFTER_NOT, BEFORE, BEFORE_NOT, ON, ON_NOT, ON_OR_AFTER, ON_OR_AFTER_NOT, ON_OR_BEFORE, ON_OR_BEFORE_NOT

Not all operators accept one value. Some operators do not require any value, some operators require two values, and some operators accept any number of values. Consider the following examples:

Unary operators: The EMPTY and EMPTY_NOT operators do not accept any values. For example: ?q=companyName EMPTY
Ternary operators: The BETWEEN, BETWEEN_NOT, WITHIN, and WITHIN_NOT operators accept two values. For example: ?q=id BETWEEN_NOT [1, 42]
N-ary operators: The ANY_OF and ANY_OF_NOT operators do accept one or any higher number of values. For example: ?q=id ANY_OF [1, 2, 3, 4, 5]

You can find the fields available for filtering in the metadata. Fields you can filter by have the x-ns-filterable parameter. For information about metadata, see Working with Resource Metadata.

For information about using datetime fields in queries, see Using Datetime Fields.

Note:

During record collection filtering, REST web services uses the underlying N/query SuiteScript module, which uses a different date format than the formats outlined in Using Datetime Fields.

The following is an example of a simple query.

Note:

The spaces in URLs are encoded. The following examples are presented without encoding for clarity.

          GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=email START_WITH barbara

The response is a collection of customer record instances where the value in the email field starts with the value of barbara. The result is a collection resource containing links to resources that match query criteria. The response could be similar to the following:

          {
    "links": [
        {
            "rel": "self",
            "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?limit=1000&offset=0"
        }
    ],
    "items": [
        {
            "links": [
                {
                    "rel": "self",
                    "href": "https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer/107"
                }
            ],
            "id": "107"
        },
    ],
    "totalResults": 1
}

When your condition value contains spaces, you should use quotation marks around the constraint, for instance, firstname IS "Barbara Allen". See the following additional query examples:

Find customer by company name (string value):

GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=companyname START_WITH "Another Company"
Find inactive customers (boolean value):

GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=isinactive IS true
Find customers created in 2019 (date value, AND operator):

GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=dateCreated ON_OR_AFTER "1/1/2019" AND dateCreated BEFORE "1/1/2020"
Find customers with high or low credit limit (number constraint, OR operator):

GET https://demo123.suitetalk.api.netsuite.com/services/rest/record/v1/customer?q=creditlimit GREATER_OR_EQUAL 1000 OR creditlimit LESS_OR_EQUAL 10

Note:

When you join more than one conditions, the AND logical operator has priority over the OR logical operator. To change this behavior, use parentheses as follows: "(“ and “)”.

Related Topics