Query syntax

The query syntax for the metadata query API is similar to that of a SQL database. To query for all files and folders that match a contract metadata template with a contract value of over $100 the following metadata query could be created.

{
  "from": "enterprise_123456.contractTemplate",
  "query": "amount >= :value",
  "query_params": {
    "value": 100
  },
  "fields": [
    "name",
    "metadata.enterprise_123456.contractTemplate.amount"
  ],
  "ancestor_folder_id": "5555"
}

In this case the from value represents the scope and templateKey of the metadata template, and the ancestor_folder_id represents the folder ID to search within, including its subfolders.

The fields parameter

By default, this API will only return the base-representation of a file or folder, which includes their id, type, and etag values. To request any additional data the fields parameter can be used to query any additional fields, as well as any metadata associated to the item.

For example:

• created_by will add the details of the user who created the item to the response.
• metadata.<scope>.<templateKey> will return the base-representation of the metadata instance identified by the scope and templateKey.
• metadata.<scope>.<templateKey>.<field> will return all fields in the base-representation of the metadata instance identified by the scope and templateKey plus the field specified by the field name. Multiple fields for the same scope and templateKey can be defined.

The query parameter

The query parameter represents the SQL-like query to perform on the selected metadata instance. This parameter is optional, and without this parameter the API would return all files and folders for this template.

Every left hand field name, like amount, needs to match the key of a field on the associated metadata template. In other words, you can only search for fields that are actually present on the associated metadata instance. Any other field name will result in the error returning an error.

The query_params parameter

To make it less complicated to embed dynamic values into the query string, an argument can be defined using a colon syntax, like :value. Each argument that is specified like this needs a subsequent value with that key in the query_params object, for example:

{
  ...,
  "query": "amount >= :amount AND country = :country",
  "query_params": {
    "amount": 100,
    "country": "United States"
  },
  ...
}

Logical operators

A query supports the following logical operators.

Operator
AND	Matches when all the conditions separated by AND are TRUE.
OR	Matches when any of the conditions separated by OR is TRUE.
NOT	Matches when the preceding condition(s) is not TRUE.
LIKE	Matches when the template field value matches a pattern. Only supported for string values. See pattern matching for more details. See additional limitations below.
NOT LIKE	Matches when the template field value does not match a pattern. Only supported for string values. See pattern matching for more details. See additional limitations below.
ILIKE	Identical to LIKE but case insensitive. See additional limitations below.
NOT ILIKE	Identical to NOT LIKE but case insensitive. See additional limitations below.
IN	Matches when a template field value equals any value in a specified list. Use explicitly defined query_params arguments for each list item, such as amount IN (:arg1, :arg2, :arg3). By design, the IN operator isn’t supported for multiselect fields in metadata queries and returns a 400 error.
NOT IN	Similar to IN but when the template field value matches none of the arguments provided in the list.
IS NULL	Matches when the template field value is null.
IS NOT NULL	Matches when the template field value is not null .
HASANY	Matches when the field value is equal to any of the arguments provided. The format requires each item in the list to be an explicitly defined query_params argument, for example, amount HASANY (:arg1, :arg2, :arg3).
HASALL	Matches when the field value has a match for all the arguments provided. The format requires each item in the list to be an explicitly defined query_params argument, for example, amount HASALL (:arg1, :arg2, :arg3).
HASANCESTOR	Matches when the field value is a descendant of any argument provided. The format requires each item in the list to be an explicitly defined query_params argument, for example, amount HASANCESTOR (:arg1, :arg2, :arg3). Supported only for the taxonomy MD field.

Any match on a string or enum field is case sensitive except when using the ILIKE operator.

Comparison operators

A query supports the following comparison operators.

Operator
=	Ensures a template field value is equal to the specified value
>	Ensures a template field value is greater than the specified value
<	Ensures a template field value is less than the specified value
>=	Ensures a template field value is greater than or equal to the specified value
<=	Ensures a template field value is less than or equal to the a specified value
<>	Ensures a template field value is not equal to the a specified value

Bit-wise and arithmetic operators are not supported by the Metadata Query API.

Pattern matching

The LIKE, NOT LIKE, ILIKE, and NOT ILIKE operators match a string to a pattern. The pattern supports the following reserved characters.

• % The percent sign represents zero, one, or multiple characters, for example %Contract matches Contract, Sales Contract, but not Contract (Sales),
• _ The underscore represents a single character, for example Bo_ matches Box, Bot, but not Bots,

Both of these reserved characters can be used before, after, or in between other characters. A pattern can include multiple reserved characters, for example Box% (____) would match Box Contract (2020).

An example query would looks something like this. Note that the %-wrapped string is not in the query attribute but in the list of query_params.

{
  ...,
  "query": "country ILIKE :country",
  "query_params": {
    "country": "%United%"
  },
  ...
}

The backslash character \ can be used to escape the % or _ characters if those need to be matched literally, for example 20\% would match the literal value of 20%.