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. Query language gives you more nuanced and explicit control over search parameters, making it especially useful in cases where precise changes to a query might yield different results.
If you want to search across all supported resource types and resource attributes and don't need ordered search results, or if you prefer to use the Console interface to specify search parameters, you don't 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 the search.
When you're ready to run a query, see Querying Resources for instructions.
Query Basics
The following examples show the basic syntax of a query.
To query for resources that meet one or more conditions described by a condition statement that uses comparison operators:
query <resourceType> resources where <conditions>
To query for resources that meet one or more conditions and to include sorted resource attributes in the results:
query <resourceType> resources return <fieldName> where <conditions> sorted by <fieldName> <order>
Or, to query for resources that have any attribute with a value that matches what you specify:
query <resourceType> resources return <fieldName> 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 later clauses. Query statements always begin with the wordquery
.return
- Specifies which resource attributes to include in the expanded or enhanced view of search results. For more information, see Return Attributes.where
- Matches resources to the specifiedconditions
. For more information, see 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. For more information, see Matching.sorted by
- Orders resources according tofieldName
in the order specified byorder
. Without this clause, Search lists results by creation date in descending order, with the newest resources listed first. For more information, see Sorting.
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 next clauses apply when you run the query. You can specify either the resource type name (for example,database
orgroup
) orall
. If you specifyall
, Search searches for matches to the specifiedconditions
across 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 the text you're submitting is a resource query.
Return Attributes
For users of the SDK or CLI, the return
clause indicates what extended resource attributes you want to see included with search results returned by the query. You can use the return
clause to force the return of specific resource attributes or all resource attributes in search results, giving you more details about each resource.
By default, search results display a limited, common set of resource attributes for any matching resource. In the Console, these resource attributes include the display name, resource type, OCID, compartment, lifecycle state, and time created. In the SDK or CLI, basic resource search results also include the availability domain and any tags associated with the resource. When a query finds a match outside the common resource attributes, then expanded search results include and highlight the specific, matching attributes alongside the basic results. With the return
clause, you can get more details about matching search results even if the query doesn't otherwise elicit a match in the resource attributes you want to see.
In a query, the return
clause consists of the following:
return <fieldName>
The fieldName
keyword identifies an attribute of a resource. For example,
when the resource type targeted by the query is an instance, specifying shape
indicates that you want to see the shape of all matching instances. (In the case of instances
only, the fieldName
can identify an attribute of a related resource, such as
the private IP address on an instance's VNIC attachment. Specifying
attachedVnic.privateIp
indicates that you want to see the private IP
address of the attachment between a VNIC and an instance.)
The additional details available for a particular resource depends on what resource attributes were indexed for the resource type. To find the names of all indexed resource attributes for a resource type, use the SDK or CLI to run a query that includes the return
clause, but instead of specifying the field name, specify that you want to see allAdditionalFields
. For example:
query instance resources return allAdditionalFields
This query produces a response that shows all indexed resource attributes for the instance resource type. (The Console can't display these other fields, except for instance resources when you apply the resource type filter, which is why you must use the SDK or CLI to retrieve the information.) In search results, the available additional details (and the structure of that information) depends on the matching resource type. Furthermore, you must have the necessary permission to see the resource attributes that you requested to see with your query.
return
clause with the where
clause to get results with specific details while also applying one or more conditions. For example:query instance resources return allAdditionalFields where assignedEntityType = 'PRIVATE_IP'
This query produces a response that shows all indexed resource attributes for all instance resources that have an assignedEntityType
field with a value of PRIVATE_IP
.
Restrictions
You can only use the return
clause against one resource type at a time. The query can't specify multiple resource types if you include the return
clause. If you want, though, you can specify more than one field to return for a particular resource type.
You can't include both the allAdditionalFields
keyword and individual field names in the same query. You can either specify the allAdditionalFields
keyword or you can specify or more individual field names, separated by commas.
Conditions
The where
clause applies conditions
that filter 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 chosen value
of that attribute are
evaluated. Each field is associated with a field type. The field type tells you the
expected format for any value in that field. What kind of operation
you
can use in a conditions
statement depends on the field type.
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 chosen resource type matches or does not match the
value
, according to the operation. In a query, you must enclose
any string or date-time value in opening and closing straight single quotes (ˈ) or
double 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. |
The following table lists some examples of resource attributes that belong to each
category of supported field types. (As previously described, the field type tells
you the expected format for a given field and the kind of operation
you can pair it with in a conditions
statement.)
The table does not include every possible example for a given field type nor does it include examples from every resource-type. If you want to know what format the Search service expects for a specific resource attribute, you can use the command line interface or API to find out more about resource attributes. You can also consult the API documentation. 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 Search.
Type | Example Resource Attributes |
---|---|
String | Display names, lifecycle states, availability domains, tags, CIDR blocks, and URLs |
Integer | Size or length of a resource |
Rational | Available data storage |
Boolean | Whether a feature is enabled or configured, whether a resource is healthy, whether a resource is public or private, whether something is the latest version, and whether something is allowed |
Date-time | Creation dates, last-updated dates, last-indexed dates, and scheduled maintenance reboots |
Grouping Conditions
By including more than one condition statement in a query, you can refine results according to several criteria. You can group conditions by using the logical operators &&
(ampersands, to indicate a logical AND), ||
(vertical bars, to indicate a logical OR), or the logical IN operator. For example:
licenseModel = 'LICENSE_INCLUDED' && dataStoragePercentage > 40 && lifecycleState != 'FAILED'
In the preceding example, all results have LICENSE_INCLUDED
as the value in the field named licenseModel
, a value greater than 40 for the field named dataStoragePercentage
, and a value in the lifecycleState
field that's anything other than FAILED
.
You can't 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 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 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 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's not FAILED
for the value of the field named lifecycleState
.
licenseModel IN ['LICENSE_INCLUDED', 'BRING_YOUR_OWN_LICENSE'] && dbWorkload IN ['OLTP', 'DW', 'AJD', 'APEX']
LICENSE_INCLUDED
or BRING_YOUR_OWN_LICENSE
as the value in the field named licenseModel
and a value of OLTP
, DW
, AJD
, or APEX
as the value in the field named dbWorkload
. The preceding example can take the place of more complex query conditions, such as the following:(licenseModel = 'LICENSE_INCLUDED' && dbWorkload = 'OLTP') || (licenseModel = 'BRING_YOUR_OWN_LICENSE' && dbWorkload = 'OLTP') || (licenseModel = 'LICENSE_INCLUDED' && dbWorkload = 'DW') || (licenseModel = 'BRING_YOUR_OWN_LICENSE' && dbWorkload = 'DW') || (licenseModel = 'LICENSE_INCLUDED' && dbWorkload = 'AJD') || (licenseModel = 'BRING_YOUR_OWN_LICENSE' && dbWorkload = 'AJD') || (licenseModel = 'LICENSE_INCLUDED' && dbWorkload = 'APEX') || (licenseModel = 'BRING_YOUR_OWN_LICENSE' && dbWorkload = 'APEX')
((licenseModel = 'LICENSE_INCLUDED' || licenseModel = 'BRING_YOUR_OWN_LICENSE') && (dbWorkload = 'OLTP' || dbWorkload = 'DW' || dbWorkload = 'AJD' || dbWorkload = 'APEX'))
Both of these examples use the logical OR operator to effectively express the same conditions in more verbose terms than the example that uses the logical IN operator instead.
Search doesn't perform left-to-right evaluation to reduce ambiguity or clarify intent.
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' |
EEE is optional. MMM can also be
expressed as MMMM . TimeZone is also
optional. If TimeZone is omitted, UTC is used. |
<yyyy>-<MM>-<dd>T<HH>:<mm>:<ss>Z
|
'2018-06-19T16:15:41Z' |
Time in UTC. ' |
You must observe spacing. Interpret dashes, colons, commas, and the characters 'T' and 'Z' literally. To interpret placeholder values in the preceding table, see 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 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".
The matching
clause queries all indexed fields for matches, but ignores
special characters, including any punctuation.
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 don't 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 don't 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.
Querying Multiple Resource Types
You can query more than one resource types at a time 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 several 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 several 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 several resource types where some resource types share conditions while others don't:
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.