Dashboards Query Language (DQL)

Dashboards Query Language (DQL) is a simple text-based query language used to filter data in OpenSearch Dashboards. For example, to display your site visitor data for a host in the United States, you would enter geo.dest:US in the search field, as shown in the following image.

DQL and query string query (Lucene) language are the two search bar language options in Discover and Dashboards. To compare these language options, see Discover and Dashboard search bar.

Setup

To follow this tutorial in OpenSearch Dashboards, expand the following setup steps.

PUT testindex
{
  "mappings" : {
    "properties" :  {
      "date" : {
        "type" : "date",
        "format" : "yyyy-MM-dd"
      }
    }
  }
}

PUT /testindex/_doc/1
{
  "title": "The wind rises",
  "description": "A biographical film",
  "media_type": "film",
  "date": "2013-07-20",
  "page_views": 100
}

PUT /testindex/_doc/2
{
  "title": "Gone with the wind",
  "description": "A well-known 1939 American epic historical film",
  "media_type": "film",
  "date": "1939-09-09",
  "page_views": 200
}

PUT /testindex/_doc/3
{
  "title": "Chicago: the historical windy city",
  "media_type": "article",
  "date": "2023-07-29",
  "page_views": 300
}

PUT /testindex/_doc/4
{
  "article title": "Wind turbines",
  "media_type": "article",
  "format": "2*3"
}

Search for terms

By default, DQL searches in the field set as the default field on the index. If the default field is not set, DQL searches all fields. For example, the following query searches for documents containing the words rises or wind in any of their fields:

rises wind

The preceding query matches documents in which any search term appears regardless of the order. By default, DQL combines search terms with an or. To learn how to create Boolean expressions containing search terms, see Boolean operators.

To search for a phrase (an ordered sequence of words), surround your text with quotation marks. For example, the following query searches for the exact text “wind rises”:

"wind rises"

Hyphens are reserved characters in Lucene, so if your search term contains hyphens, DQL might prompt you to switch to Lucene syntax. To avoid this, surround your search term with quotation marks in a phrase search or omit the hyphen in a regular search.

Reserved characters

The following is a list of reserved characters in DQL:

\, (, ), :, <, >, ", *

Use a backslash (\) to escape reserved characters. For example, to search for an expression 2*3, specify the query as 2\*3:

2\*3

Search in a field

To search for text in a particular field, specify the field name before the colon:

title: rises wind

The analyzer for the field you’re searching parses the query text into tokens and matches documents in which any of the tokens appear.

DQL ignores white space characters, so title:rises wind and title: rises wind are the same.

Use wildcards to refer to field names containing spaces. For example, article*title matches the article title field.

Field names

Specify the field name before the colon. The following table contains example queries with field names.

Wildcards

DQL supports wildcards (* only) in both search terms and field names, for example:

t*le: *wind and rise*

Ranges

DQL supports numeric inequalities using the >, <, >=, and <= operators, for example:

page_views > 100 and page_views <= 300

You can use the range operators on dates. For example, the following query searches for documents containing dates within the 2013–2023 range, inclusive:

date >= "2013-01-01" and date < "2024-01-01"

You can query for “not equal to” by using not and the field name, for example:

not page_views: 100

Note that the preceding query returns documents in which either the page_views field does not contain 100 or the field is not present. To filter by those documents that contain the field page_views, use the following query:

page_views:* and not page_views: 100

Boolean operators

DQL supports the and, or, and not Boolean operators. DQL is not case sensitive, so AND and and are the same. For example, the following query is a conjunction of two Boolean clauses:

title: wind and description: epic

Boolean operators follow the logical precedence order of not, and, and or, so in the following example, title: wind and description: epic is evaluated first:

media_type: article or title: wind and description: epic

To dictate the order of evaluation, group Boolean clauses in parentheses. For example, in the following query, the parenthesized expression is evaluated first:

(media_type: article or title: wind) and description: epic

The field prefix refers to the token that immediately follows the colon. For example, the following query searches for documents in which the title field contains windy or documents containing the word historical in any of their fields:

title: windy or historical

To search for documents in which the title field contains windy or historical, group the terms in parentheses:

title: (windy or historical)

The preceding query is equivalent to title: windy or title: historical.

To negate a query, use the not operator. For example, the following query searches for documents that contain the word wind in the title field, are not of the media_type article, and do not contain epic in the description field:

title: wind and not (media_type: article or description: epic)

Queries can contain multiple grouping levels, for example:

title: ((wind or windy) and not rises)

Object fields

To refer to an object’s inner field, list the dot path of the field.

To index a document containing an object, follow the steps in the object field type example. To search the name field of the patient object, use the following syntax:

patient.name: john

Nested fields

To refer to a nested object, list the JSON path of the field.

To index a document containing an object, follow the steps in the nested field type example.

To search the name field of the patients object, use the following syntax:

patients: {name: john}

To retrieve documents that match multiple fields, specify all the fields. For example, consider an additional status field in the following document:

{ 
  "status": "Discharged",
  "patients": [ 
    {"name" : "John Doe", "age" : 56, "smoker" : true},
    {"name" : "Mary Major", "age" : 85, "smoker" : false}
  ] 
}

To search for a discharged patient whose name is John, specify the name and the status in the query:

patients: {name: john} and status: discharged

You can combine multiple Boolean and range queries to create a more refined query, for example:

patients: {name: john and smoker: true and age < 57} 

Doubly nested fields

Consider a document with a doubly nested field. In this document, both the patients and names fields are of type nested:

{
  "patients": [
    {
      "names": [
        { "name": "John Doe", "age": 56, "smoker": true },
        { "name": "Mary Major", "age": 85, "smoker": false}
      ]
    }
  ]
}

To search the name field of the patients object, use the following syntax:

patients: {names: {name: john}}

In contrast, consider a document in which the patients field is of type object but the names field is of type nested:

{
  "patients": 
  {
    "names": [
      { "name": "John Doe", "age": 56, "smoker": true },
      { "name": "Mary Major", "age": 85, "smoker": false}
    ]
  }
}

To search the name field of the patients object, use the following syntax:

patients.names: {name: john}