Sorting and Filtering

Suggest Edits

Requests with No Filters

Requests without a filter or limit on the number of objects returned will return as many items as the caller is permitted to see, within resource usage limits. Although there are cases where this is desirable, most API method usage will want to limit the result set to a much narrower range. A query to return all assets across all accounts and projects, for example, would look something like this:

query AllAssets {
  assets {
    id
    properties
    project{
      id
    }
  }
}

This might result in the following response (in this case, you only have access to two Assets):

{
  "data": {
    "assets": [
      {
        "id": "DrNiE-Upt0vpdiHV8pUr",
        "properties": {
          "Name": "Test"
        },
        "project": {
          "id": "c5Q0ouSUcA716cuW7As2"
        }
      },
      {
        "id": "BVcYLu-qXaNSIbw9VEwg",
        "properties": {},
        "project": {
          "id": "c5Q0ouSUcA716cuW7As2"
        }
      }
    ]
  }
}

For most users of OnePlace, this response would include thousands if not millions of objects.

Add a single filter

Any attribute in the API may be used in a filter. For example, users often want to limit the search space to a single Project.

You can filter for a single Project by adding a filter argument that limits results to a specific project id.

query AssetsInProject {
  assets(filter:[eq "$.project.id" "c5Q0ouSUcA716cuW7As2"]) {
    id
    properties
  }
}

This simple example demonstrates several important elements of a filter:

  • A filter is applied with the filter parameter. Every root field supports a filter parameter, as do most fields which return a list.
  • The filter expression is a list.
  • Each expression begins with short string which specifies an operator. The remaining items in the list are the arguments to the operator. (A list of filter operators is below.)
  • A string beginning with a dollar sign is a reference to a field in the object graph. The dollar sign stands for the object being filtered, and the dot behaves as it does in most popular programming languages. Notably, the fields referenced in the filter need not be selected in the result set. In the example above, the use of $.project.id in the filter does not require the inclusion of project { id } in the selection set.

Combine Multiple Filters with and and or

Multiple filters may be applied using the and and or operators. For example, to get all of the assets created in a particular project by a particular user, use the following query:

query AssetsInProjectByCreator {
  assets(filter:[
    and
      [eq "$.project.id" "c5Q0ouSUcA716cuW7As2"],
      [eq "$.creator.id" "Tx6QqoOigi0_5mUAshom"]]) {
    id
    properties
  }
}

Note that filter expressions may be nested as arguments within other filter expressions.

Filter Operators

You’ll notice that we are using the eq operator to look for project and creator ids that equal the ones we have provided in the filter. Here is the list of other operators you might use:

Boolean Operators

The three boolean operators have the usual semantics. They only accept boolean-valued arguments.

  • and returns true if both of its arguments are true, false otherwise.
  • not returns true if its argument is false, false otherwise.
  • or returns true if either of its arguments are true, false otherwise.

Comparison Operators

The six comparison operators should also be familiar. They accept a variety of argument types, including numbers, strings, and timestamps.

  • eq returns true if both of its arguments represent the same value.
  • gt returns true if its first argument is strictly greater than its second.
  • gte returns true if its first argument is greater than or equal to its second.
  • lt returns true if its first argument is strictly less than its second.
  • lte returns true if its first argument is less than or equal to its second.
  • neq returns true if its arguments do not represent the same value.

Arithmetic Operators

The arithmetic operators need little explanation. The only important remark applies to the division operator div and the modulus operator mod. Division is symmetric, meaning that where the divisor and dividend have different signs, both the quotient and remainder will be negative. Therefore the result of the expression [mod "$.someValue" 7] could be between -6 and 6, depending on the sign of the dividend. These all accept only numeric arguments.

  • add returns the sum of its arguments.
  • div returns the quotient of its first argument divided by its second.
  • mod returns the remainder of its first argument divided by its second.
  • mul returns the product of its arguments.
  • neg returns the negation of its argument.
  • sub returns the difference of its arguments.

Pattern Matching Operators

The wildcard operators, ilike and like, accept a pattern argument in which underscore _ matches any single character, and the percent sign % matches zero or more characters. Regular expressions use POSIX syntax. These operators only accept string arguments.

  • ilike returns true if the pattern in its first argument is a case-insensitive match to the text in its second.
  • like returns true if the pattern in its first argument matches the text in its second.
  • regex returns true if the POSIX regular expression in its first argument matches the text in its second.

List Operators

The list operators only accept list arguments, with the exception of in which accepts a scalar first argument. The lists may be of numbers, strings,

  • in returns true if its first argument is an element of its second.
  • within returns true if every element of its first argument is an element of its second.
  • overlap returns true if any element of its first argument is also an element of its second.

Miscellaneous Operators

  • extract expects a JSON-valued first argument and one or more additional string arguments. The second and successive arguments define a path from the root of the JSON value to the desired value. In particular, the properties field of an asset is JSON-valued.
  • intersect returns true if its first argument intersects its second. Both arguments must be geometries.
  • literal returns its first argument. This is useful for quoting strings that begin with $ or for preventing lists from being interpreted as expressions. For example, a filter might contain the following expression to extract the "Status" from an asset's properties and check it against a set of values.
[in 
  [extract "$.properties" "Status"]
  [literal ["assigned", "in-progress"]]
]
  • ref returns its first argument dereferenced by its second and successive arguments. The expression parser expands "$.project.id" to [ref "project" "id"], for example.

Limiting the number of results

For large result sets, the count parameter limits the number of objects included in the results.

query MyQuery {
  assets(filter: [gt "$.createdAt" "2023-01-01T00:00:00Z"], count: 1) {
    id
    properties
  }
}

Ordering the results

Use the order parameter to control the order of the result set. It accepts a list of order terms, each a scalar field name plus an Asc or Desc suffix. The most common will likely be createdAtAsc or updatedAtDesc.

query MyQuery {
  assets(order: [createdAtDesc], count: 1) {
    id
    properties
  }
}

Pagination

Large result sets can be retrieved in manageable chunks using the pagination parameters: order, count, and before or after. The first two are explained in the sections above. The latter two are more complex, but enable efficient pagination with only a little more effort than LIMIT and OFFSET from SQL. We'll explain after here, since that will probably be the most commonly used; before is the same, but applied in the opposite direction. Let's start with a common use case, where we might be successively requesting blocks of assets in the order in which they were created:

query MyQuery {
  assets(
      order: [createdAtAsc] 
      after: [eq "$.id" "t9iDF7Zm6tkFHDnKfId4"]
      count: 10) {
    id
    properties
  }
}

The after parameter accepts a filter expression. It could match zero, one, or many objects. If it matches zero objects, then the query behaves as if the parameter were not present. If it matches exactly one object, then the results returned begin immediately after the matching object, in terms of the given order. A multiple object match is exactly like the single match case, except that the most important matching object is the one latest in the order. Simply put, results begin immediately after the latest object which matches the filter expression. Using the after parameter in practice is very similar to using OFFSET and LIMIT, except instead of adding LIMIT to OFFSET and submitting another query, here you need to update the after parameter's filter expression with the relevant details from the last object in the prior result set.

The following example illustrates how to paginate recent updates, and to include updates to records that have already been seen. After the first set of results has been retrieved, the following query demonstrates how to get the next set of results.

query MyQuery {
  assets(
      order: [updatedAtAsc] 
      after: [or
               [lt "$.updatedAt" "2022-01-05T09:44:19.353143Z"]
               [and 
                 [eq "$.id" "t9iDF7Zm6tkFHDnKfId4"]
                 [eq "$.updatedAt" "2022-01-05T09:44:19.353143Z"]
               ]
             ]
      count: 10) {
    id
    properties
  }
}

Here, the after expression contains an or expression with two subexpressions. The first subexpression matches anything that changed before the last object in the prior result set. The second subexpression matches the last element in the prior result set, but only if it hasn't changed.

Filtering JSON columns

Several objects have JSON-valued columns, in particular, an asset's properties field. To filter on a value embedded in the JSON, use the extract operator.

query MyQuery {
  assets(filter:[eq [extract "$.properties" "Name"] "HQ"]) {
    id
    createdAt
    properties
  }
}

Updated over 1 year ago