Basic queries

Use the SELECT clause, along with FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT to search and aggregate data.

Among these clauses, SELECT and FROM are required, as they specify which fields to retrieve and which indexes to retrieve them from. All other clauses are optional. Use them according to your needs.

Syntax

The complete syntax for searching and aggregating data is as follows:

  1. SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
  2. FROM index_name
  3. [WHERE predicates]
  4. [GROUP BY expression [, ...]
  5. [HAVING predicates]]
  6. [ORDER BY expression [IS [NOT] NULL] [ASC | DESC] [, ...]]
  7. [LIMIT [offset, ] size]

Fundamentals

Apart from the predefined keywords of SQL, the most basic elements are literal and identifiers. A literal is a numeric, string, date or boolean constant. An identifier is an OpenSearch index or field name. With arithmetic operators and SQL functions, use literals and identifiers to build complex expressions.

Rule expressionAtom:

expressionAtom

The expression in turn can be combined into a predicate with logical operator. Use a predicate in the WHERE and HAVING clause to filter out data by specific conditions.

Rule expression:

expression

Rule predicate:

expression

Execution Order

These SQL clauses execute in an order different from how they appear:

  1. FROM index
  2. WHERE predicates
  3. GROUP BY expressions
  4. HAVING predicates
  5. SELECT expressions
  6. ORDER BY expressions
  7. LIMIT size

Select

Specify the fields to be retrieved.

Syntax

Rule selectElements:

selectElements

Rule selectElement:

selectElements

Example 1: Use * to retrieve all fields in an index:

  1. SELECT *
  2. FROM accounts
account_numberfirstnamegendercitybalanceemployerstateemailaddresslastnameage
1AmberMBrogan39225PyramiILamberduke@pyrami.com880 Holmes LaneDuke32
16HattieMDante5686NetagyTNhattiebond@netagy.com671 Bristol StreetBond36
13NanetteFNogal32838QuilityVAnanettebates@quility.com789 Madison StreetBates28
18DaleMOrick4180 MDdaleadams@boink.com467 Hutchinson CourtAdams33

Example 2: Use field name(s) to retrieve only specific fields:

  1. SELECT firstname, lastname
  2. FROM accounts
firstnamelastname
AmberDuke
HattieBond
NanetteBates
DaleAdams

Example 3: Use field aliases instead of field names. Field aliases are used to make field names more readable:

  1. SELECT account_number AS num
  2. FROM accounts

| num :— | 1 | 6 | 13 | 18

Example 4: Use the DISTINCT clause to get back only unique field values. You can specify one or more field names:

  1. SELECT DISTINCT age
  2. FROM accounts

| age :— | 28 | 32 | 33 | 36

From

Specify the index that you want search. You can specify subqueries within the FROM clause.

Syntax

Rule tableName:

tableName

Example 1: Use index aliases to query across indexes. To learn about index aliases, see Index Alias. In this sample query, acc is an alias for the accounts index:

  1. SELECT account_number, accounts.age
  2. FROM accounts

or

  1. SELECT account_number, acc.age
  2. FROM accounts acc
account_numberage
132
636
1328
1833

Example 2: Use index patterns to query indexes that match a specific pattern:

  1. SELECT account_number
  2. FROM account*

| account_number :— | 1 | 6 | 13 | 18

Where

Specify a condition to filter the results.

OperatorsBehavior
=Equal to.
<>Not equal to.
>Greater than.
<Less than.
>=Greater than or equal to.
<=Less than or equal to.
INSpecify multiple OR operators.
BETWEENSimilar to a range query. For more information about range queries, see Range query.
LIKEUse for full-text search. For more information about full-text queries, see Full-text queries.
IS NULLCheck if the field value is NULL.
IS NOT NULLCheck if the field value is NOT NULL.

Combine comparison operators (=, <>, >, >=, <, <=) with boolean operators NOT, AND, or OR to build more complex expressions.

Example 1: Use comparison operators for numbers, strings, or dates:

  1. SELECT account_number
  2. FROM accounts
  3. WHERE account_number = 1
account_number
1

Example 2: OpenSearch allows for flexible schema,so documents in an index may have different fields. Use IS NULL or IS NOT NULL to retrieve only missing fields or existing fields. OpenSearch does not differentiate between missing fields and fields explicitly set to NULL:

  1. SELECT account_number, employer
  2. FROM accounts
  3. WHERE employer IS NULL
account_numberemployer
18 

Example 3: Deletes a document that satisfies the predicates in the WHERE clause:

  1. DELETE FROM accounts
  2. WHERE age > 30

Group By

Group documents with the same field value into buckets.

Example 1: Group by fields:

  1. SELECT age
  2. FROM accounts
  3. GROUP BY age
idage
028
132
233
336

Example 2: Group by field alias:

  1. SELECT account_number AS num
  2. FROM accounts
  3. GROUP BY num
idnum
01
16
213
318

Example 4: Use scalar functions in the GROUP BY clause:

  1. SELECT ABS(age) AS a
  2. FROM accounts
  3. GROUP BY ABS(age)
ida
028.0
132.0
233.0
336.0

Having

Use the HAVING clause to aggregate inside each bucket based on aggregation functions (COUNT, AVG, SUM, MIN, and MAX). The HAVING clause filters results from the GROUP BY clause:

Example 1:

  1. SELECT age, MAX(balance)
  2. FROM accounts
  3. GROUP BY age HAVING MIN(balance) > 10000
idageMAX (balance)
02832838
13239225

Order By

Use the ORDER BY clause to sort results into your desired order.

Example 1: Use ORDER BY to sort by ascending or descending order. Besides regular field names, using ordinal, alias, or scalar functions are supported:

  1. SELECT account_number
  2. FROM accounts
  3. ORDER BY account_number DESC
account_number
18
13
6
1

Example 2: Specify if documents with missing fields are to be put at the beginning or at the end of the results. The default behavior of OpenSearch is to return nulls or missing fields at the end. To push them before non-nulls, use the IS NOT NULL operator:

  1. SELECT employer
  2. FROM accounts
  3. ORDER BY employer IS NOT NULL
employer
 
Netagy
Pyrami
Quility

Limit

Specify the maximum number of documents that you want to retrieve. Used to prevent fetching large amounts of data into memory.

Example 1: If you pass in a single argument, it’s mapped to the size parameter in OpenSearch and the from parameter is set to 0.

  1. SELECT account_number
  2. FROM accounts
  3. ORDER BY account_number LIMIT 1
account_number
1

Example 2: If you pass in two arguments, the first is mapped to the from parameter and the second to the size parameter in OpenSearch. You can use this for simple pagination for small indexes, as it’s inefficient for large indexes. Use ORDER BY to ensure the same order between pages:

  1. SELECT account_number
  2. FROM accounts
  3. ORDER BY account_number LIMIT 1, 1
account_number
6