SQL Search

Observability provides the SQL search feature. This feature allows users to search Observability indexes using SQL queries. To search for Observability, instead of using regular SQL queries, you need to specify the field names and index names in the logs. In traditional SQL this would be "SELECT column_name FROM table_name", whereas in Observability it would be "SELECT field_name FROM index_name". So, when you query Observability, you search using the field names and index names in the logs.

Basic SQL Query

warning

• All the outputs below consist of examples for the purpose of informing the user.

Use the SELECT clause in combination with FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT to search and collect data.

   SELECT * FROM  ``sample-index1`

In the returned answer, the parts called 'schema' represent fields, in other words columns. 'Datarow' are the values corresponding to these fields:

• schema: Specifies the field and types for all fields.
• data_rows: An array of results. Each result represents one matching row(document).
• total: The total number of rows(documents) in the index.
• size: The number of results to return in one response.
• status: The HTTP response status Observability returns after running the query.

Syntax

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

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

Complex Queries

Besides simple SFW (SELECT-FROM-WHERE) queries, the SQL plugin supports complex queries such as subquery, join, union, and minus.

	 SELECT * FROM sample-index1 WHERE age > 30 AND gender = 'M' ORDER BY balance DESC

Joins

Observability SQL supports inner joins, cross joins, and left outer joins.

Joins have a number of constraints:

1. You can only join two indexes.

2. You must use aliases for indexes (for example, people p).

3. Within an ON clause, you can only use AND conditions.

4. In a WHERE statement, don’t combine trees that contain multiple indexes. For example, the following statement works:

   WHERE (a.type1 > 3 OR a.type1 < 0) AND (b.type2 > 4 OR b.type2 < -1)

   The following statement does not:

   WHERE (a.type1 > 3 OR b.type2 < 0) AND (a.type1 > 4 OR b.type2 < -1)

5. You can’t use GROUP BY or ORDER BY for results.

6. LIMIT with OFFSET (e.g. LIMIT 25 OFFSET 25) is not supported.

Example: Inner Join

Inner join creates a new result set by combining columns of two indexes based on your join predicates. It iterates the two indexes and compares each document to find the ones that satisfy the join predicates. You can optionally precede the JOIN clause with an INNER keyword.

SQL Query:

SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
JOIN employees_nested e
ON a.account_number = e.id

Example: Cross Join

Cross join, also known as cartesian join, combines each document from the first index with each document from the second. The result set is the the cartesian product of documents of both indexes. This operation is similar to the inner join without the ON clause that specifies the join condition.

SQL Query:

SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
JOIN employees_nested e

Example: Left Outer Join

Use left outer join to retain rows from the first index if it does not satisfy the join predicate. The keyword OUTER is optional.

SQL Query:

SELECT
a.account_number, a.firstname, a.lastname,
e.id, e.name
FROM accounts a
LEFT JOIN employees_nested e
ON a.account_number = e.id

Subqueries

A subquery is a complete SELECT statement used within another statement and enclosed in parenthesis. From the explain output, you can see that some subqueries are actually transformed to an equivalent join query to execute.

Example:

 SELECT a1.firstname, a1.lastname, a1.balance
FROM accounts a1
WHERE a1.account_number IN (
  SELECT a2.account_number
  FROM accounts a2
  WHERE a2.balance > 10000
)

Example:

 SELECT a.f, a.l, a.a
FROM (
  SELECT firstname AS f, lastname AS l, age AS a
  FROM accounts
  WHERE age > 30
) AS a

Match Query

To use matchquery or match_query, pass in your search query and the field name that you want to search against:

   SELECT account_number, address FROM sample-index1 WHERE MATCHQUERY(address, 'Holmes') AND age > 30