アセット クエリ言語 (AQL) 構文を使用する

Assets Query Language (AQL) is a language format used in Assets to create search queries for one or more objects. Using AQL, you can return any object or group of objects in a search, filter objects, modify objects, create custom fields, automate processes, and more.

AQL can be used in the Advanced AQL search or within an object custom field. It is one of Assets' most powerful and dynamic features.

基本的な構文

The basic syntax of an AQL query is <attribute> <operator> <value/function>. When the attributes of the objects match the operator and value specified, one or more objects will be returned by the query.

Example: Owner = "Ted Anderson"

This AQL query would return all objects where the Owner is Ted Anderson. Note the quotations around "Ted Anderson" because there is a space in the value.

特殊文字の構文

AQL には定義された構文があり、正確に入力する必要があります。

• AQL では、大文字と小文字が区別されません。

• 値または属性にスペースが含まれている式を使用する場合は、上記の "Ted Anderson" の例のように、値を囲む引用符を含める必要があります。

• If you are using an expression where the value or attribute contain quotation marks, you must escape the quotes by preceding them with backslashes. For example, if you have an object name such as 15" Screen, to search for it enter: 15\” Screen

• AQL で指定する属性名は、アセット スキーマに存在する必要があります。そうでない場合、AQL は無効とみなされます。

AQL has a defined syntax that must be entered exactly. Here are some key guidelines:

• AQL is not case-sensitive.

• If the value or attribute contains spaces, enclose it in quotations. For example: "Ted Anderson".

• If the value or attribute contains quotation marks, escape them using backslashes. For example, for an object name like 15" Screen, use: 15\” Screen.

• The attribute name you specify in the AQL must exist in your Assets schema. If not, the AQL will be invalid.

AQL は次のタスクを実行する際に役立ちます。

• アセットのオブジェクトを自動化する

• アセット クエリ言語でオブジェクトを検索

• アセット オブジェクト フィールドを設定する

ドット表記

Dot notation in AQL is used to traverse a reference chain of objects. The format <attribute>.<attribute> <operator> <value/function> allows you to return information based on objects referenced by a parent object.

For example: “Belongs to Department”.Name = HR

Here, the Employee object type has a referenced attribute called "Belongs to Department". This query will return all employees who belong to the HR department.

Since the referenced attribute contains spaces, it is enclosed in double quotes for proper formatting.

キーワード

In AQL, you can use keywords to return objects based on their properties rather than specific attributes. The syntax is as follows: <keyword> <operator> <value/function>

For instance, to return all objects of a specific object type, you can use the objectType keyword.

Example: objectType = “Employee”

The table below lists supported keywords and examples:

"Host" オブジェクト タイプを持つすべてのオブジェクトを返す AQL クエリは次のとおりです。

演算子

Operators in AQL help you create more detailed and logical expressions for querying objects. Here’s a table that describes the operators supported:

組み合わせ演算子

You can use operators like AND and OR to combine multiple conditions and create more complex AQL queries. Here’s an example:

関数 

You can use different functions to supply dynamic values to AQL queries.

参照関数

Reference functions in AQL allow you to run a query on a subset of objects, typically of a specific reference type. They take two arguments: an AQL query and an optional reference-type argument.

• AQL argument: This is the query you want to run, which could include other reference functions.

• Reference type argument: This is optional and allows you to limit the query to a particular reference type.

connectedTickets() 関数の使用

The connectedTickets() function in AQL is used to filter objects that have Jira work items connected to them. You can specify a JQL query to filter the specific Jira work you want to include in your search. If no JQL query is provided, the function will return all objects that have connected Jira issues.

You must provide a JQL as an argument to that AQL function (e.g. object having connectedTickets(Project = VK)).

objectTypeAndChildren() 関数を使用する

The objectTypeAndChildren() function in AQL is used to return objects of a specific object type along with their associated child objects. This function is useful when you want to retrieve not just a parent object but also its related child objects within the same query.

This AQL query returns all objects and child objects from the "Asset Details" object type:

参照、関数、AQL を組み合わせる

関数、参照、AQL は、さまざまな優れた方法で組み合わせられます。たとえば、あるオブジェクトに添付されている 1 つのカスタム フィールドに複数のオブジェクト参照を追加して、これらの参照を次のような特定のキーで検索できます。

object HAVING inboundReferences(Key IN (${MyCustomField${0}}))

または、ドット表記を使用して次のような特定のラベルで検索できます。

object HAVING inboundReferences(Label IN (${Portfolios.label${0}}))

These above two queries make use of Assets placeholders. Learn more about Assets placeholders.

By default, the Assets object detail view presents Unresolved connected tickets.

発注

You can order the results of your AQL query by appending the following suffix:

pgsqlCopyEditorder by [AttributeName|label] [asc|desc]

• If no order by clause is specified, the default order is ascending by the object type's label attribute.

• If the attribute in the order by clause is a reference type, you can use dot notation to order by an attribute of the referenced object.

  • For example, to order by the referenced Department of an Employee object, use: order by Employee.Department. This can be done in unlimited depth, but each additional dot will impact query performance.

• Missing values will appear at the top of the list when using ascending order (asc).

• The attribute used in the order by clause must exist in the Assets schema. If it does not, the AQL will be invalid.

• You can use the label placeholder to order objects by their configured label.

• 複数の属性による順序づけはサポートされていません。

If the attribute specified in the order by clause is not found in the results, the order will be arbitrary.