Dashboards Query Language (DQL)

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.

Setup

Use the following steps to prepare sample data for querying.

Step 1: Set up mappings for the index

On the main menu, select Management > Dev Tools to open Dev Tools. Send the following request to create index mappings:

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

copy

Step 2: Ingest the documents into the index

In Dev Tools, ingest the following documents into the index:

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

copy

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
}

copy

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

copy

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

copy

Step 3: Create an index pattern

Follow these steps to create an index pattern for your index:

On the main menu, select Management > Dashboards Management.
Select Index patterns and then Create index pattern.
In Index pattern name, enter testindex*. Select Next step.
In Time field, select I don't want to use the time filter.
Select Create index pattern.

For more information about index patterns, see Index patterns.

Step 4: Navigate to Discover and select the index pattern

On the main menu, select Discover. In the upper-left corner, select testindex* from the Index patterns dropdown list. The main panel displays the documents in the index, and you can now try out the DQL queries described on this page.

The Object fields and Nested fields sections provide links for additional setup needed to try queries in those sections.

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

copy

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"

copy

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

copy

Search in a field

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

title: rises wind

copy

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.

Query	Criterion for a document to match	Matching documents from the `testindex` index
`title: wind`	The `title` field contains the word `wind`.	1, 2
`title: (wind OR windy)`	The `title` field contains the word `wind` or the word `windy`.	1, 2, 3
`title: “wind rises”`	The `title` field contains the phrase `wind rises`.	1
`title.keyword: The wind rises`	The `title.keyword` field exactly matches `The wind rises`.	1
`title: wind`	Any field that starts with `title` (for example, `title` and `title.keyword`) contains the word `wind`	1, 2
`articletitle: wind`	The field that starts with `article` and ends with `title` contains the word `wind`. Matches the field `article title`.	4
`description:*`	Documents in which the field `description` exists.	1, 2

Wildcards

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

t*le: *wind and rise*

copy

Ranges

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

page_views > 100 and page_views <= 300

copy

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"

copy

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

not page_views: 100

copy

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

copy

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

copy

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

copy

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

copy

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

copy

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

title: (windy or historical)

copy

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)

copy

Queries can contain multiple grouping levels, for example:

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

copy

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

copy

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}

copy

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

copy

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}

copy

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}}

copy

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}

copy