Oracle Cloud Infrastructure Documentation

Search Language Syntax

This topic describes the basics of the query language for Search, including an explanation of syntax and rules so you can create your own queries. Queries apply search conditions to specific resource types and let you sort results. If you want to search across all supported resource types and resource attributes and do not need ordered search results, you do not need to construct a query. Instead, you can search for a partial or exact match of free-form text without applying query language syntax to your search.

When you are ready to run a query, see Querying Resources for instructions.

Query Basics

The following examples show the basic syntax of a query:

query <resourceType> resources where <conditions> sorted by <fieldName> <order>

Or:

query <resourceType> resources matching <keywords>

Search ignores white space, indentation, and line breaks. Sample queries include indentation to improve readability. For the purposes of demonstrating syntax only, angle brackets (<>) and italicized text indicate variables, which can consist of one or more keywords.

In a query, clauses include the following:

  • query - (Required) Selects which resources to return based on subsequent clauses. Query statements always begin with the word query.
  • where - Matches resources to the specified conditions.
  • matching - Matches resources to the specified text regardless of whether the text matches exactly, matches the resource type, or appears in an indexed resource attribute.
  • sorted by - Orders resources according to fieldName in the order specified by order. If you do not include this clause, Search lists results by creation date in descending order, with the newest resources listed first.

Clauses are optional unless indicated otherwise. For matching purposes, you can use the where clause and the matching clause either separately or together.

In the query clause, you specify the following information:

  • resourceType - (Required) Specifies the resource type to which the subsequent clauses apply when you run the query. You can specify either the resource type name (for example, database or group) or all. If you specify all, Search searches for the conditions against all resource types. You can query for individual resource types, but not family types. For a list of supported resource types, see the Supported Resources section of Overview of Search.
  • resources - (Required) Specifies that this is a resource query.

Conditions

The where clause applies conditions that act as a filter to restrict the results returned by Search. You can specify one or more condition statements. For more information about multiple conditions, see Grouping Conditions.

In a query, conditions consist of the following:

<fieldName> <operation> <value>

The fieldName keyword is the resource attribute against which the operation and desired value of that attribute are evaluated. Each field is associated with a field type. The type of operation you can use in your conditions filter depends on the field type. The API documentation includes a reference for each supported resource type that specifies attributes, their field types, and any restrictions. For more information, see the Supported Resources section of Overview of Resource Query.

In query conditions, an operation is a comparison operator that applies to the value in the statement. The value keyword refers to the value of the fieldName you specified. Search evaluates whether the specified attribute of the desired resource type matches or does not match the desired value, according to the operation. You must enclose a value in opening and closing straight single quotes (ˈ).

The following table describes supported operations for resource queries:

Operation Description Supported Field Types Case-sensitive? Example

=

Equals, or exact matching for strings

String, integer, rational, Boolean, date-time

No

If the value was ˈbackUpˈ, it would match "backup", "BACKUP", "BackUp", "backUp", or any other variation in casing.

!=

Does not equal

String, integer, rational, Boolean, date-time

No

If the value was ˈbackUpˈ, it would match anything that does not equal "backUp", "backup", or any other variation in casing. It also would match anything that does not contain the characters 'backup' in that order.

==

Strictly equals

String

Yes

If the value was ˈbackUpˈ, it would only match "backUp" and no other variation in casing.

!==

Strictly does not equal

String

Yes

If the value was ˈbackUpˈ, it would match "backup", "BACKup", or anything except "backUp", with that exact casing.

=~

Contains

String

No

If the value was ˈbackUpˈ, it would match anything that equals "backup", "BACKUP", "BackUp", "backUp", or any other variation in casing, or contains those characters in that order, alongside other characters.

>=

Greater than or equal to

Integer, rational, date-time

Not applicable

For a query where you have size >= 5 as the condition, all results have a value of 5 or greater in the field named size.

>

Greater than

Integer, rational, date-time

Not applicable

For a query where you have size > 5 as the condition, all results have a value of greater than 5 in the field named size.

<=

Less than or equal to

Integer, rational, date-time

Not applicable

For a query where you have size <= 5 as the condition, all results have a value of 5 or less in the field named size.

<

Less than

Integer, rational, date-time

Not applicable

For a query where you have size < 5 as the condition, all results have a value of 5 or less in the field named size.

Date and Time Values

You can specify date and time values by using any of the following pattern string formats:

Format Examples Comments

<yyyy>-<MM>-<dd> <HH>:<mm>:<ss> <TimeZone>

'2018-06-19 16:15:41 PDT', '2018-06-19 16:15:41 -08:00'

TimeZone is optional. If TimeZone is omitted, UTC is used.

<EEE>, <d> <MMM> <yyyy> <HH>:<mm>:<ss> <TimeZone>

'Tue, 19 Jun 2018 16:15:41 +0300', '19 June 2018 16:15:41'

TimeZone is optional. If TimeZone is omitted, UTC is used.

<yyyy>-<MM>-<dd>T<HH>:<mm>:<ss>Z

2018-06-19T16:15:41Z

Time in UTC. 'T' and 'Z' are case-sensitive.

You must observe spacing. Interpret dashes, colons, commas, and the characters 'T' and 'Z' literally. To interpret placeholder values in the preceding table, you can refer to the following pattern syntax:

Letter Date or Time Component Presentation

y

Year

Year

M

Month in year

Month

d

Day in month

Day

H Hour in day (from 00-23) Number
m Minute in hour Number
s Seconds in minute Number
E Day in week Text

Repeating pattern letters indicate their exact presentation. For example, 'HH' means you must use '00' and not '0' to represent midnight. Similarly, 'EEE' means 'Tue' and not 'Tuesday'. Likewise, 'MM' requires '09' instead of '9' to represent the month of September.

TimeZone is optional, but in your chosen format, you can specify TimeZone in any of the following ways:

  • Name. You can specify a time zone by its name, such as GMT or PDT. Values are case-insensitive.
  • GMT offset value. You can specify a time zone according to its GMT offset. For example, GMT-08:00. Values are case-insensitive.
  • ISO 8601 time zone. You can specify a time zone according to ISO 8601 standards. For example, -08, -0800, or -08:00.

Instead of using one of the preceding formats, you can also specify a date-time value as the constant now. The constant now represents the current time to the level of granularity of seconds in a minute.

Lastly, you can add or subtract time intervals from any date-time values. For example, you can query for resources that were created within five minutes of a specific time. Search supports the following time intervals:

Letter Date or Time Component

s

Seconds

m

Minutes

h

Hours

d Days
w Weeks

To specify a time interval in relation to a date-time value, use one of the following formats:

  • now - 3h
  • 2018-06-19 16:15:41 PDT + 1h

Matching

For matching purposes, instead of or in addition to using a where clause with conditions, you might want to use the matching clause. The matching clause obviates the need to specify conditions (that contain a field name, operation, and value). A matching clause effectively queries all indexed fields by applying the = (equals) operator along with the text you specify. However, it does so without strictly requiring an exact match. For example, the following query uses a matching clause to behave the same way as a free text search: query all resources matching 'instance'. The query produces results that match all resources and resource attributes that contain the word "instance".

Sorting

The last clause of a resource query is the sorted by clause and is optional. The sorted by clause orders the results returned by Search based on the field name and lists them according to the order you specify. By default, if you do not specify sort order, results are always sorted by date-time created in descending order.

In the sorted by clause, you can specify the following:

  • fieldName - The field that Search uses to sort results. You can specify any field of any resource. Resources that do not contain the field you specify are listed after the resources that do.
  • order - You can specify either asc or desc. Specifying asc lists results in ascending order. Specifying desc lists results in descending order.

Grouping Conditions

You can group multiple conditions by using either the logical operators && (ampersands, to indicate a logical AND) or || (vertical bars, to indicate a logical OR). For example:

licenseModel = 'LICENSE_INCLUDED' && dataStoragePercentage > 40 && lifecycleState != 'FAILED'

You cannot combine two different logical operators in the same query unless you wrap parentheses around one group of predicates. (Multiple conditions can only use the same logical operator otherwise.) For example:

(licenseModel = 'LICENSE_INCLUDED' && dataStoragePercentage > 40) || lifecycleState != 'FAILED'

In the preceding example, all results returned will have either “LICENSE_INCLUDED” as the value in the field named “licenseModel” and a value greater than 40 for the field named “dataStoragePercentage” or the value of their “lifecycleState” field name is anything other than “FAILED”.

The following group is also acceptable:

licenseModel = 'LICENSE_INCLUDED' && (dataStoragePercentage > 40 || lifecycleState != 'FAILED')

In the preceding example, all results returned will have “LICENSE_INCLUDED” as the value in the field named “licenseModel” and either a value greater than 40 as the value for the field named “dataStoragePercentage” or anything that is not “FAILED” for the value of the field named “lifecycleState”.

Search does not perform left-to-right evaluation to reduce ambiguity or clarify intent.

Querying Multiple Resource Types

You can query multiple resource types at once by joining queries. Each query can have its own conditional clause. If the queries that you want to join have different "where" conditions, then the syntax is different from when you have queries for multiple resource types that share the same "where" condition.

The basic syntax for a query for multiple resource types is as follows:

query <resourceType>, <resourceType> resources

For example:

query group, user resources

The preceding example query returns all groups and all users in the tenancy.

The following shows the syntax for a query for multiple resource types with conditions, but where the conditions are the same for all resource types:

query <resourceType>, <resourceType> resources where <conditions>

For example:

query group, user resources where displayName = 'administrator'

The preceding example query returns all groups with the display name "administrator" and all users with the display name "administrator," with any variation in casing.

If you need to apply differing conditions to any resource type, you must use a union keyword instead of comma separation between the joined queries. The following shows the syntax for a query for multiple resource types where some of the resource types share conditions while others do not:

query <resourceType>, <resourceType> resources where <conditions> union <resourceType> resources

For example:

query group, user resources where displayName = 'administrator' union compartment resources

The preceding example returns all groups with the display name "administrator" and all users with the display name "administrator," with any variation in casing, and all compartment resources.

Or, for example:

query group resources union user resources where displayName = 'administrator' union compartment resources

The preceding example returns all groups and all compartments. It also returns all users with the display name "administrator," with any variation in casing.

Optionally, you can add the sorted by clause to the end of the query to order all results in ascending or descending order.