List or search for records in the Data API

To list all records in a resource, or to search for specific resources, you send a GET request to the resource. Searching, pagination and various other functionality can be accessed with specific querystring parameters appended to the endpoint.

If you haven't already, you should first read the resource specification documentation.

Listing all records

To list all records in the resource, simply send a GET to that resource with no other parameters. This will retrieve the first page of that resource (defaulting to the maximum of 2000 records in the page) - along with all related resources according to the resource specification.

For example, GET /API/V3/Customer/ will retrieve the first 2000 customers in the customer database.

In a more complex example:

GET /API/V3/Product+AdvancedPricing+(Variant+AdvancedPricing)/

... will retrieve up to 2000 Product records, with:

• associated AdvancedPricing records embedded into the Product node
• any related Variant records embedded into the Product node (used with advanced options & subproducts features)
• any AdvancedPricing records associated to any of the Variant records will be embedded in the Variant (each individual variant can have its own pricing matrix if required).

Searching a resource

To search a resource you submit the same GET request described above for listing, but you pass in querystring parameters relating to fields you wish to filter by.

So for example if you wanted to retrieve all Product entries that had a ProductAttribute of "Material" set to "Aluminium", you could send the following to the API:

GET /API/V3/ProductAttributes?label=Material&value=Aluminium

If you wanted to retrieve e.g. the title & price of each product made of aluminium, you could join back to the Product resource in the resource specification:

GET /API/V3/ProductAttributes+Product?label=Material&value=Aluminium

Wildcards

You can use % as a wildcard in your search queries. So:

GET /API/V3/Order+Orderline?order_number=ORD%25

... would retrieve all orders starting with ORD (note %25 is a url encoded %).

"Or" searches

By default if you specify multiple search parameters, the API will expect all parameters to match. However you can pass in a special configuration parameter _match=any in the querystring and it will switch to an OR search.

So:

GET /API/V3/Order+Orderline?order_number=ORD%25&username=%25foo%25&_match=any

... would retrieve all orders starting with ORD OR assigned to someone with foo in their username.

Multiple values for a field

Sometimes you want to find all records that match a number of values - like an SQL IN clause. You can achieve this by passing the field multiple times with different values you want to accept.

In this scenario (assuming _match is not set to any), values for the same field will be treated as an OR, while different field names will be treated as AND.

For example:

GET /API/V3/Product?title=Hoodie%&title=Jersey%&sku=01380&sku=0139%

... will retrieve all products who's titles start with "Hoodie" or "Jersey", where their sku is either 01380 or their sku starts with 0139. If represented as an SQL clause it would look like this:

SELECT *
FROM products
WHERE (product.title LIKE 'Hoodie%' OR product.title LIKE 'Jersey%')
  AND (product.sku = '01380' OR product.sku LIKE '0139%')

Advanced searching

Sometimes just being able to specify values for fields isn't enough, and you need to be able to construct more complex queries, including a wider range of logical operators (eg > , <, AND and OR and NOT).

To do this, you can specify a special _search=[search string] parameter.

For example:

GET /API/V3/ProductAttributes?_search=last_modified>'2022-01-01' AND label='knit'

... will retrieve any product attributes that were last modified sometime after 1st January 2022, and have a label of "knit".

These search strings use a highly restricted subset of SQL - any variation from this subset will result in an error. You can:

• specify any field in the primary api resource you are searching. You can't specify other fields. You can also compare fields (eg _search=custom_data1 > custom_data2).
• Use logical operators: AND, NOT, OR, != (not equal), =, >, >=, <, <=.
• Strings should be quoted (eg 'hello world') or numbers for comparison
• Use brackets for grouping
• Some special functions. are permitted - e.g. ISNULL() (finds if a value is null) - eg _search=ISNULL(custom_data1)
• a few date operators. NOW, INTERVAL, DAY MONTH and YEAR can be used for such constructs as: mydatefield > NOW() - INTERVAL 3 MONTH - although note that these parameters will only work for fields that are internally stored as dates (some API xs:datetime fields are not, trial and error is the only way to find out)

If you are struggling to get this working, you client may require you to URL encode the search string.

Pagination

By default ZEST will return the first page of request when you list records or search. If you don't specify a limit, it will retrieve the maximum of 2000 records.

However you can control the size of a page by passing the special configuration parameter _results=[number_of_results] in the querystring. So the following request would retrieve the first 5 orders:

GET /API/V3/Orders?_results=5

To retrieve the next page of results you can specify a _start parameter - which will offset the first record. So in the example above, you could retrieve page 2 of the results by requesting:

GET /API/V3/Orders?_start=5&results=5

Note, like an array, this index is zero based - so _start=5 will return the 6th record (the record at index 5).

To assist with pagination, the ResultSet node returned with any search / list request will contain: - startResultIndex - the index of the first node in the results. - totalResults - the total available results. - totalResultsReturned the number of records in this page of results.

Limiting returned fields

You can limit the fields in a response by passing a _fields querystring parameter. By default this receives a comma separated list of fields you would like to retrieve.

For example, to limit the fields retrieved for a product to title and sku you could retrieve:

GET /API/V3/Products/10512?_fields=title,sku

You can also do this with a general search:

GET /API/V3/Products?title=foo%&_fields=title,sku

Limiting related fields

If you are passing a resource specification to retrieve related records, you can pass a more complex field definition to also limit those fields:

1. Separate resources in the definition with a pipe |
2. For related resources, prefix the field definition with the name of the resource, followed by a colon (:). For example: Orderlines:sku,quantity will retrieve just the sku & quantity fields of related Orderline records.

The following example will retrieve all product that start with foo along with any related Inventory or Variant records, limiting the fields for each resource to the following:

• Products fields to sku and title
• Inventory fields to just the quantity field
• Variants fields to code and title

GET /API/V3/Products+Inventory+Variants?title=foo%&_fields=sku,title|Variants:code,title|Inventory:quantity

Response

The response to any GET request to the Data API is always of the same format. It contains a wrapper ResultSet node which gives you metadata about the result. This node then contains all of the actual results.

An example result for an Order+Orderline resource specification may look like (http status: 200 OK)

<?xml version="1.0"?>
<ResultSet startResultIndex="0" totalResults="276" totalResultsReturned="2" xmlns="https://www.clientwebsite.nz" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://www.clientwebsite.nz/API/V3/Order.xsd">
  <Order href="https://www.clientwebsite.nz/API/V3/Order/ORD0000002" id="Model::Transactions::ORD0000002">
    <OrderLine href="https://www.clientwebsite.nz/API/V3/OrderLine/ORD0000002" id="Model::Orderline::ORD0000002">
      <code >ORD0000002</code >
      <quantity>1</quantity>
      <sku>01120</sku>
      <!-- etc -->
    </OrderLine>
    <address1></address1>
    <!-- etc -->
  </Order>
  <Order href="https://www.clientwebsite.nz/API/V3/Order/ORD0000003" id="Model::Transactions::ORD0000003">
    <OrderLine href="https://www.clientwebsite.nz/API/V3/OrderLine/ORD0000003" id="Model::Orderline::ORD0000003">
       <!-- etc -->
    </OrderLine>
    <!-- etc -->
  </Order>
</ResultSet>

No results found

If no results are found, the server will still return a 200 OK, but with an empty ResultSet:

<?xml version="1.0" encoding="utf-8" ?>
<ResultSet session="" startResultIndex="0" totalResults="0" totalResultsReturned="0" xmlns="http://www.zeald.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.zeald.com https://www.clientwebsite.nz/API/V3/Order.xsd">
</ResultSet>

Invalid search

If you search for an invalid field, or your advanced search was not acceptable, you will receive a 406 Not Acceptable status code along with the following response:

<?xml version="1.0" encoding="utf-8" ?>
<rsp stat="fail" version="3.0">
    <error>
        <code>406</code>
        <msg>Invalid fields specified: some_invalid_field</msg>
    </error>
</rsp>

See error responses for more information.