> For the complete documentation index, see [llms.txt](https://knowledgebase.fabricdata.com/xytech/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://knowledgebase.fabricdata.com/xytech/rest-api/query-parameters-and-filters.md).

# Working with Query Parameters and Filters

### Query Parameter

**GET** and **PATCH** List endpoints require query parameters.

This section describes the syntax and options used for the query parameter. This is a mandatory parameter for List documents.

The standard format for a “query” parameter is to add the parameter as *query={}* to the end of a GET request for a List document after the parameter delimiter (“?”), where the value of query= is a JSON object:

`http://{APIbase_url}/documentList?query={key: value}`

Such as:

`http://{APIbase_url}/jmJobList?query={"job_no": "12345"}`

**Note:** The query parameter is supported with GET and PATCH requests of List type documents and is **not** supported by GET requests for Setup or Maintenance documents.

In the simplest form, the value is a single piece of information, such as a string or integer. In more complex forms, the value is a JSON object containing specific formats as described below.

See section regarding **URL encoding** requirements: [URL encoding of special characters](#url-encoding-of-special-characters)

### Query Filters

#### String or Number

To return items that match a specified string. The string can either be letters or numbers. Wildcard ‘%’ can be used. Remember to URL encode the % sign.

<table><thead><tr><th width="213">Description</th><th>Parameter values</th></tr></thead><tbody><tr><td>Syntax</td><td><code>query= {"field":"value"}</code></td></tr><tr><td>Examples</td><td><code>query={"job_desc":"Big Apple Live"}</code><br><code>query={"job_desc":"%Big%"}</code><br><code>query={"job_no":101101}</code></td></tr><tr><td>Multiple key/value pairs</td><td>To specify multiple key/value pairs, separate each key/value pair with a comma<br><code>query={"cust_id":"123","job_type_no":"4"}</code><br><code>query={"cust_id":"123","job_type_no":"4","active":"Y"}</code></td></tr><tr><td></td><td>Note: When specifying multiple key/value pairs, the API will return only items that match ALL specified criteria.</td></tr></tbody></table>

#### Range

To return items that fall between a specified minimum and maximum numeric value.

<table><thead><tr><th width="214">Description</th><th>Parameter values</th></tr></thead><tbody><tr><td>Syntax</td><td><br><code>query="field":{"$range":[lower_limit, upper_limit]}</code><br></td></tr><tr><td>Examples</td><td><p><br><code>query={"job_no":{"$range":[100, 199]}}</code></p><p><code>query={"wo_begin_dt":{"$range":["2023-12-01","2023-12-31"]}}</code></p><p><code>query={"wo_begin_dt":{"$range":["2023-06-01T09:00","2023-06-01T17:00"]}}</code></p></td></tr><tr><td></td><td></td></tr></tbody></table>

#### In

To return items that match one of the values provided in a given set of values. See section below on wildcards [Searching for multiple wildcard values](<#Searching for multiple wildcard values>)

<table><thead><tr><th width="212">Description</th><th>Parameter values</th></tr></thead><tbody><tr><td>Syntax</td><td><code>query={“field”:{"$in":[“value_1”,”value_2”, … ”value_n”]}}</code></td></tr><tr><td>Examples</td><td><code>query={“job_no”:{"$in":[100, 105, 110, 119]}}</code></td></tr></tbody></table>

#### Null (empty)

To return items that have NULL values. Note: Put pipe characters around NULL to differentiate it from the literal string “NULL”.

<table><thead><tr><th width="271">Description</th><th>Parameter Values</th></tr></thead><tbody><tr><td>Syntax</td><td><code>query={"field": "|NULL|"}</code></td></tr><tr><td>Examples</td><td><code>query={"phone_number":"|NULL|"}</code></td></tr></tbody></table>

#### ISNULL

(26.3) An explicit test for whether a field is null, as an alternative to the `"|NULL|"` value above. The value must be a JSON boolean: `true` returns records where the field is null, `false` returns records where it is not.

<table><thead><tr><th width="266">Description</th><th>Parameter Values</th></tr></thead><tbody><tr><td>Syntax</td><td><code>query={"field":{"$isnull":true}}</code></td></tr><tr><td>Examples:</td><td></td></tr><tr><td>Is null</td><td><code>query={"phone_number":{"$isnull":true}}</code></td></tr><tr><td>Is not null</td><td><code>query={"phone_number":{"$isnull":false}}</code></td></tr><tr><td></td><td>Note: the value must be <code>true</code> or <code>false</code>, not the string "true" or a number.</td></tr></tbody></table>

For character-based fields, a null test also matches blank and whitespace-only values, so `{"$isnull":true}` returns records where the field is null **or** empty, and `{"$isnull":false}` excludes both. Numeric and date fields match true nulls only. The `"|NULL|"` form behaves identically.

#### NE (not equal)

To return items that do not match the specified number, string, or NULL.

<table><thead><tr><th width="266">Description</th><th>Parameter Values</th></tr></thead><tbody><tr><td>Syntax</td><td><code>query={"field":{"$ne":"value"}}</code></td></tr><tr><td>Examples:</td><td></td></tr><tr><td>Not string</td><td><code>query={"job_desc ":{"$ne":"Big Apple Live"}}</code></td></tr><tr><td>Not number</td><td><code>query={"cust_id":{"$ne":1001}}</code></td></tr><tr><td>Not null</td><td><code>query={"jm_phase_external_key":{"$ne":"|NULL|"}}</code></td></tr><tr><td>Not like</td><td><code>query={"wo_desc":{"$ne":"Test%"},"wo_type_no": 83}</code></td></tr></tbody></table>

#### Greater than and less than new query parameters

To return items where values are greater than or less than a given value.

<table><thead><tr><th width="195">Description</th><th width="73">Abv</th><th>Parameter values</th></tr></thead><tbody><tr><td>Syntax</td><td></td><td><code>query={"field":{"option": "value"}}</code></td></tr><tr><td>Examples:</td><td></td><td></td></tr><tr><td>Greater Than</td><td>$gt</td><td><code>query={"title_no":{"$gt": 106438}}</code></td></tr><tr><td>Greater Than Or Equal</td><td>$gte</td><td><code>query={"date_added":{"$gte":"2022-07-26T00:00:00"}}</code></td></tr><tr><td>Less Than</td><td>$lt</td><td><code>query={"date_added":{"$lt":"2022-07-26T00:00:00"}}</code></td></tr><tr><td>Less Than Or Equal</td><td>$lte</td><td><code>query={"date_added":{"$lte":"2022-07-26T00:00:00"}}</code></td></tr><tr><td></td><td></td><td>Values can be numeric or dates.</td></tr></tbody></table>

Full GET example for greater than

```json
curl --location --globoff 'http://{APIbaseURL}/LibMasterList?resultcolumns={"L": ["master_no", "master_desc", "date_added","desc_3"]}&query={"title_no":{"$gt": 1102}}' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic ******' \
--data ''
```

#### NOTIN (Not in)

(11.1) Ability to query where a field's value does not match an array of supplied values.\
Usage example:\
`{{server}}/PmProjectList?query={"project_desc":{"$notin":["test", "Sarah"]}}`\
Applies to GET queries.

#### LIKEAND (Like and)

(11.1) Ability to define an array of matching values that all have to match regardless of the order defined. `{{server}}/LibMasterList?{"master_desc":{"$LIKEAND":["%Genesis%","%XHD%"]}`\
Applies to GET queries.

#### OR (Or groups)

(26.3) Ability to combine several groups of criteria where a record only has to match one of them.

`$or` is a top-level key rather than a field operator. Its value is an array of filter objects. Fields **within** a group are AND'd together, the groups are then OR'd with each other, and the whole `$or` block is AND'd with any other criteria in the same query.

<table><thead><tr><th width="213">Description</th><th>Parameter values</th></tr></thead><tbody><tr><td>Syntax</td><td><code>query={"$or":[{group_1}, {group_2}, ... {group_n}]}</code></td></tr><tr><td>Examples</td><td><code>query={"$or":[{"job_type_no":4,"active":"Y"},{"job_type_no":9}]}</code></td></tr><tr><td>Combined with other criteria</td><td><code>query={"cust_id":"123","$or":[{"job_type_no":4,"active":"Y"},{"job_type_no":9}]}</code></td></tr><tr><td></td><td>Note: Each group must be a JSON object, and at least one group must be supplied.</td></tr></tbody></table>

The combined example above returns jobs for customer 123 that are either an active type 4, or any type 9:

`cust_id = 123 AND ((job_type_no = 4 AND active = 'Y') OR job_type_no = 9)`

Any of the filter operators described above can be used inside a group, for example a range in one group and a wildcard match in another:

`query={"$or":[{"job_no":{"$range":[100, 199]}},{"job_desc":{"$likeand":["%Genesis%","%XHD%"]}}]}`

{% hint style="warning" %}
**Known limitation: only one `$or` is applied per query.** A query cannot combine two separate OR blocks, so a filter of the form `(A OR B) AND (C OR D)` cannot be expressed.

* Supplying `$or` twice with the same spelling applies only the **last** one. The earlier block is discarded and no error is returned.
* Supplying `$or` twice with different casing (for example `$or` and `$OR`) merges every group into a **single** OR set, giving `A OR B OR C` rather than `(A OR B) AND C`.
* An `$or` nested inside another `$or` group is rejected with an "Invalid filter" error.

Supply one `$or` per query, and place any criteria that must always apply as ordinary top-level keys alongside it. See [Known Issues](/xytech/rest-api/known-issues.md).
{% endhint %}

**Fields allowed in a group.** Only fields on the primary List table can be used inside an `$or` group. A field that comes from a related sub-table will fail the request with a "multi-part identifier could not be bound" error rather than returning results, so check the field is part of the List document itself before using it in a group. This is a known limitation - see [Known Issues](/xytech/rest-api/known-issues.md).

Fields inside a group are subject to the same parameter validation and user column rights as top-level fields.

### Query Filter Tips

#### NULL Values

The Null parameter returns any record that has a null value for the specified key, which indicates that no value has ever been set. This differentiates it from a 0 value for numbers and true or false values for Boolean properties.

**Text fields:** for character-based fields a null test also matches blank and whitespace-only values, so empty strings are returned alongside true nulls. Date and numeric fields match true nulls only.

**Note:** Not all fields support null values. If possible, check the OpenAPI definition whether the field allows nulls.

**Values**

* String values are not case-sensitive.
* DateTime values should be provided in a valid ISO date format.

#### URL encoding of special characters

When using HTML special characters as part of the query value, they must be URL encoded.

Example: to use a wildcard query such as "**%dave%"**, the % needs substituting with **%25**. Once URL encoded will look like this **%25dave%25**

Example GET query with URL encoded wildcard :\
`{{server}}/MoMediaOrderList?query={"wo_desc":"`**`%25dave%25`**`"}&resultcolumns={"L": ["wo_no", "wo_desc"]}`

*(The reason why **%dave%** fails to return valid results it that **%da** is the encoding for the **Ú** character)*

This also applies to datetime values that use the offset attribute with the + sign.

To include a value of "**2023-06-01T09:00+5:00**" in a URL query parameter, substitute + with **%2b**

Example:\
`query={"wo_begin_dt":{"$range":["2023-06-01T09:00%2b5:00","2023-06-01T17:00%2b5:00"]}}`

#### Searching for multiple wildcard values

A field can be matched against several wildcard values at once. Which operator you use depends on whether **all** of the values must match or **any** of them:

<table><thead><tr><th width="130">Operator</th><th width="230">Behaviour</th><th>Example</th></tr></thead><tbody><tr><td><code>$in</code></td><td>Any one of the values may match (OR)</td><td><code>{"master_desc":{"$in":["%Tale%","%Dark%"]}}</code></td></tr><tr><td><code>$likeand</code></td><td>All of the values must match, in any order (AND)</td><td><code>{"master_desc":{"$likeand":["%Tale%","%Dark%"]}}</code></td></tr></tbody></table>

For example, to find media assets that have both "Tale" AND "Dark" in their "master\_desc" field, in any order, use `$likeand`:

`{"master_desc":{"$likeand":["%Tale%","%Dark%"]}}`

A single value can also contain more than one wildcard, but that fixes the order. The example below matches only descriptions where "Tale" appears somewhere before "Dark":

`{"master_desc":"%Tale%Dark%"}`

Remember to URL encode the % symbol (for instance if using Postman) as **%25**:

`{"master_desc":{"$likeand":["%25Tale%25","%25Dark%25"]}}`

### Result Columns Parameter

Used by the **GET** method on List and Maintenance endpoints.

**resultColumns** parameter is used to define the fields you wish to return in the response.\
Without this parameter, the response will contain all document fields which is not recommended for performance reasons.

*For example:*\
`{APIbaseurl}/JmJobList?Query={"job_no":2}&resultColumns={"L":["job_no","job_desc"]}`

Job No. and Job Description fields will be included in the response.\
Important to include the “L” as the top-level element.

**Sub-Table columns**

Many endpoints include related sub-tables in their responses. Example syntax to include specific sub-table columns.

Below example fetches a transmission order description and all it's service row numbers:

`{APIbase_url}/XmTransmissionOrder/wo_no_seq=7655-1?resultColumns={"jm_work_order":["wo_desc"],"mo_service_row":["service_row_no"]}`

*Response:*

```json
{
    "jm_work_order": [
        {
            "mo_service_row": [
                {
                    "service_row_no": {
                        "external_key": null,
                        "service_row_no": 9933
                    }
                },
                {
                    "service_row_no": {
                        "external_key": null,
                        "service_row_no": 9934
                    }
                }
            ],
            "wo_desc": "WS Transmission Test"
        }
    ]
}
```

{% hint style="success" %}
**Performance recommendation.**\
Always use the **resultColumns** parameter otherwise responses will return large numbers of fields most of which will not be required and only adds to the system performance overhead.\
In the future API v3, this will become a mandatory parameter.
{% endhint %}

### Pagination and Sort Parameters

API Pagination is available for the GET Query on List endpoints.

<table><thead><tr><th width="129">Parameter</th><th width="112">Type</th><th>Description</th></tr></thead><tbody><tr><td>pageSize</td><td>Integer</td><td>is the number of records returned per page</td></tr><tr><td>page</td><td>Integer</td><td>is the page number to return</td></tr><tr><td>sort</td><td>String</td><td>is the field to sort followed by ascending or descending option</td></tr></tbody></table>

**Sort parameters:**\
Syntax: `sort=[field1 sort, field2 sort]`\
Example: `sort=["job_desc desc", "job_no desc"]`

*Note:*\
If you don’t specify pageSize, the ‘page’ and ‘sort’ options are ignored.\
If you do specify a pageSize and do not pass a page, page will default to 1.

**Example:**\
To return the first 10 records on page 1 sorted by product\_no:

```json
GET {APIbaseURL}/JmOrgProductList?query={"active":"Y"}&resultColumns={"L":["product_no","product_desc"]}&sort=["product_no desc"]&pageSize=10&page=1
```

**Full example:**

```json
GET {APIbaseURL}/JmJobList?query={"job_no":{"$range":[1,67982]}}&resultcolumns={"L": ["job_no", "job_desc"]}&pageSize=5&page=7&sort=["job_desc desc", "job_no desc"]
```

The above example queries for jobs that have job numbers in the range of 1 to 67982, returns job number and job description fields but only the 7th page with 5 jobs sorted first by description (descending) then by job number (descending).

The response header will include a parameter called '**Pagination-Count**' which is the count of all records as a result of the query.

This will allows you to call for data in manageable payloads without exceeding memory limitations.

### Null Value Handling Parameter

This optional parameter suppresses all null value fields from the response payload. Using this parameter reduces the payload size dramatically, especially for larger queries. (v10.6+)

Applicable for all GET calls with List, Maintenance & Report endpoints.\
Values are ‘**ignore**’ or ‘**include**’ (default).\
'include' means that all null values are included in the response.

Examples:\
*URL Parameter:- nullvaluehandling=**include** (default)*

```js
{
    "L": [
        {
            "barcode": "MM915",
            "company_name": null,
            "cust_id": null,
            "master_desc": null,
            "master_no": {
                "barcode": "MM915",
                "external_key": "VX-90",
                "master_no": 915,
                "umid": null
...
```

URL Parameter:- nullvaluehandling=**ignore** (recommended)

```js
{
    "L": [
        {
            "barcode": "MM915",
            "master_no": {
                "barcode": "MM915",
                "external_key": "VX-90",
                "master_no": 915
...
```

Notice how all null value fields are omitted.

{% hint style="success" %}
**Performance Recommendation**\
It's recommended to always include this parameter with the value 'ignore', unless visibility of null values is required.
{% endhint %}

### Alternate Key Handling Parameter

This optional parameter suppresses additional key fields from the responses. If you do not need to work with key fields other than the primary key, use this parameter to keep the API call performant and reduce the processing overhead when not working with alternate key fields. (v10.6+)

Applicable for all GET calls with List, Maintenance endpoints.

Values are ‘**ignore**’ or ‘**include**’ (default). ‘include’ includes alternate keys in the response.

Example:\
URL Parameter:- alternatekeyhandling=**include** (default)

```json
{
    "L": [
        {
            "barcode": "MM915",
            "company_name": null,
            "cust_id": null,
            "master_desc": null,
            "master_no": {
                "barcode": "MM915",
                "external_key": "VX-90",
                "master_no": 915,
                "umid": null
...
```

*URL Parameter:- alternatekeyhandling=**ignore*** (recomended)

```json
{
    "L": [
        {
            "barcode": "MM915",
            "company_name": null,
            "cust_id": null,
            "master_desc": null,
            "master_no": {
                "master_no": 915
...
```

Notice how the additional key fields barcode, external\_key & umid are omitted.

{% hint style="success" %}
**Performance Recommendation**\
It's recommended to always include this parameter with the value 'ignore', unless you are working with alternate keys.
{% endhint %}

### Source Time Zone Name Header

The REST API uses Date time formats in ISO format with an optional offset value.

e.g. 2014-11-03T22:20:00+00:00

If you omit the offset value when using POST to create a record, you can use a header parameter to set the time zone your dates are using.\
The advantage of using this header approach, is that it will respect any daylight savings offset applicable to the date given.

<table><thead><tr><th width="481">Header Key</th><th>Header Value</th></tr></thead><tbody><tr><td>Source-Time-Zone-Name</td><td>{Windows Time Zone name}</td></tr></tbody></table>

e.g. header: `Source-Time-Zone-Name: Pacific Standard Time`

This example will will create records in the time zone of Pacific Standard Time. Remember to omit the offset values in your time formats.

See link for list of [Windows Time Zones](https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones?view=windows-11)

### Division

(11.1)

REST API calls support the ability to pass the Division as an override to the API API user's default Division.

An optional header called "Division-Code" exists where you can include the Division code. This will ensure the correct results are returned for the API users when using Divisions.

The API user account must have been given user access to the division to be able to successfully pass it in the API call. If not, you will receive an error message.

e.g. Division-Code : GS

Using Postman:

<figure><img src="https://920260032-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FjO1LJ0JqjxT1HpQEykwt%2Fuploads%2FRWlQh4w1UnhgxHwIqkrt%2Fimage.png?alt=media&#x26;token=4aaee428-9925-475f-8193-217cdb8a19bf" alt=""><figcaption></figcaption></figure>

This capability replicates the functionality when a user accesses the webapp with their default division, who will see data filtered by their default Division.

Applicable only to sites that use the Division feature.