OpenSearch SQL and PPL

https://docs.opensearch.org/latest/sql-and-ppl/
Admin
Documentation for SQL and PPL query languages in OpenSearch, which provide alternatives to the query...
Tokens:137,350
Snippets:1,861
Trust Score:10
Update:4 months ago
Show doc for...
Tokens:
Raw
### Span with start/end

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example combines `span`, `start`, and `end` parameters to precisely control the binning process.

```ppl
source=accounts
| bin age span=1 start=25 end=35
| fields age
| head 6
```

--------------------------------

### Start and end range

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example specifies the `start` and `end` of the binning range, ensuring that bins fall within these boundaries.

```ppl
source=accounts
| bin age start=0 end=101
| fields age
| head 1
```

--------------------------------

### Example 1: Fetching all metadata

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/describe

This example describes the accounts index.

```ppl
describe accounts
```

--------------------------------

### Install the CLI

Source: https://docs.opensearch.org/latest/sql-and-ppl/cli

Installs the opensearchsql package using pip.

```bash
pip3 install opensearchsql

```

--------------------------------

### Example response: Get all labels

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/read-resources

Response showing a list of label names.

```json
{
  "status": "success",
  "data": [
    "__name__",
    "instance",
    "job",
    "mode",
    "cpu"
  ]
}
```

--------------------------------

### Syntax Examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/eventstats

Examples demonstrating the syntax of the eventstats command.

```ppl
source = table | eventstats avg(a)
```

```ppl
source = table | where a < 50 | eventstats count(c)
```

```ppl
source = table | eventstats min(c), max(c) by b
```

```ppl
source = table | eventstats count(c) as count_by by b | where count_by > 1000
```

```ppl
source = table | eventstats dc(field) as distinct_count
```

```ppl
source = table | eventstats distinct_count(category) by region
```

--------------------------------

### Example request: Get all labels

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/read-resources

Fetches all label names from a Prometheus data source.

```http
GET /_plugins/_directquery/_resources/my_prometheus/api/v1/labels
```

--------------------------------

### POWER Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/math

Example demonstrating the POWER function, a synonym for POW.

```ppl
source=people
| eval `POWER(3, 2)` = POWER(3, 2), `POWER(-3, 2)` = POWER(-3, 2), `POWER(3, -2)` = POWER(3, -2)
| fields `POWER(3, 2)`, `POWER(-3, 2)`, `POWER(3, -2)`
```

--------------------------------

### Match query example using MATCH_QUERY

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql/functions

Example demonstrating the use of MATCH_QUERY to replace MATCH.

```sql
SELECT account_number, address
FROM accounts
WHERE address = MATCH_QUERY('Holmes')
```

--------------------------------

### Example response: Get values for a specific label

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/read-resources

Response showing values for the 'job' label.

```json
{
  "status": "success",
  "data": [
    "prometheus",
    "node_exporter",
    "alertmanager"
  ]
}
```

--------------------------------

### Example 2: Get all documents

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/syntax

A PPL query to retrieve all documents from the 'otellogs' index and limit the output to 5 rows.

```ppl
source=otellogs
| head 5
| fields severityText, `resource.attributes.service.name`, body

```

--------------------------------

### graphLookup command examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/graphlookup

Examples of the graphLookup command.

```ppl
source = employees | graphLookup employees start=reportsTo edge=reportsTo-->name as reportingHierarchy
```

```ppl
source = employees | graphLookup employees start=reportsTo edge=reportsTo-->name maxDepth=2 as reportingHierarchy
```

```ppl
source = employees | graphLookup employees start=reportsTo edge=reportsTo-->name depthField=level as reportingHierarchy
```

```ppl
source = employees | graphLookup employees start=reportsTo edge=reportsTo<->name as connections
```

```ppl
source = travelers | graphLookup airports start=nearestAirport edge=connects-->airport supportArray=true as reachableAirports
```

```ppl
source = airports | graphLookup airports start=airport edge=connects-->airport supportArray=true as reachableAirports
```

```ppl
source = employees | graphLookup employees start=reportsTo edge=reportsTo-->name filter=(status = 'active' AND age > 18) as reportingHierarchy
```

--------------------------------

### QUERY_STRING field boosting examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/full-text

Examples demonstrating how to boost fields in the QUERY_STRING function.

```sql
query_string(["Tags" ^ 2, 'Title' 3.4, `Body`, Comments ^ 0.3], ...)
```

```sql
query_string(["*"], ...)
```

--------------------------------

### Match query example using MATCHQUERY

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql/functions

Example demonstrating the use of MATCHQUERY to replace MATCH.

```sql
SELECT account_number, address
FROM accounts
WHERE MATCHQUERY(address, 'Holmes')
```

--------------------------------

### LOG10 Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/math

Example demonstrating the LOG10 function to calculate the base-10 logarithm.

```ppl
source=people
| eval `LOG10(100)` = LOG10(100)
| fields `LOG10(100)`
```

--------------------------------

### MINUTE_OF_HOUR() Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example of using the MINUTE_OF_HOUR() function to get the minute within the hour.

```ppl
source=people
| eval `MINUTE_OF_HOUR(TIME('01:02:03'))` =  MINUTE_OF_HOUR(TIME('01:02:03'))
| fields `MINUTE_OF_HOUR(TIME('01:02:03'))`
```

--------------------------------

### MINUTE_OF_DAY() Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example of using the MINUTE_OF_DAY() function to get the total minutes in the day.

```ppl
source=people
| eval `MINUTE_OF_DAY(TIME('01:02:03'))` = MINUTE_OF_DAY(TIME('01:02:03'))
| fields `MINUTE_OF_DAY(TIME('01:02:03'))`
```

--------------------------------

### Launch the CLI

Source: https://docs.opensearch.org/latest/sql-and-ppl/cli

Launches the CLI and connects to a local OpenSearch instance with basic authentication.

```bash
opensearchsql https://localhost:9200 --username admin --password admin

```

--------------------------------

### RAND Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/math

Example demonstrating the RAND function to generate a random number.

```ppl
source=people
| eval `RAND(3)` = RAND(3)
| fields `RAND(3)`
```

--------------------------------

### PI Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/math

Example demonstrating the PI function to retrieve the mathematical constant pi.

```ppl
source=people
| eval `PI()` = PI()
| fields `PI()`
```

--------------------------------

### MINUTE() Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example of using the MINUTE() function to get the minute from a time value.

```ppl
source=people
| eval `MINUTE(TIME('01:02:03'))` =  MINUTE(TIME('01:02:03'))
| fields `MINUTE(TIME('01:02:03'))`
```

--------------------------------

### Basic Syntax Examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/join

Demonstrates various ways to use the basic join command syntax, including different join types, aliases, and join criteria.

```ppl
source = table1 | inner join left = l right = r on l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | inner join left = l right = r where l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | left join left = l right = r on l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | right join left = l right = r on l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | full left = l right = r on l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | cross join left = l right = r on 1=1 table2
source = table1 | left semi join left = l right = r on l.a = r.a table2
source = table1 | left anti join left = l right = r on l.a = r.a table2
source = table1 | join left = l right = r [ source = table2 | where d > 10 | head 5 ]
source = table1 | inner join on table1.a = table2.a table2 | fields table1.a, table2.a, table1.b, table1.c
source = table1 | inner join on a = c table2 | fields a, b, c, d
source = table1 as t1 | join left = l right = r on l.a = r.a table2 as t2 | fields l.a, r.a
source = table1 as t1 | join left = l right = r on l.a = r.a table2 as t2 | fields t1.a, t2.a
source = table1 | join left = l right = r on l.a = r.a [ source = table2 ] as s | fields l.a, s.a
```

--------------------------------

### DAY_OF_YEAR Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example demonstrating the DAY_OF_YEAR function to get the day of the year.

```ppl
source=people
| eval `DAY_OF_YEAR(DATE('2020-08-26'))` = DAY_OF_YEAR(DATE('2020-08-26'))
| fields `DAY_OF_YEAR(DATE('2020-08-26'))`
```

--------------------------------

### Binning with string fields

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example demonstrates how to bin string fields by first converting them to a compatible type (e.g., using `CAST`) and then applying the `bin` command.

```ppl
source=accounts
| eval age_str = CAST(age AS STRING)
| bin age_str bins=3
| stats count() by age_str
| sort age_str
```

--------------------------------

### Run a sample SQL command

Source: https://docs.opensearch.org/latest/sql-and-ppl/cli

Executes a sample SQL query to retrieve all data from the 'accounts' table.

```sql
SELECT * FROM accounts;

```

--------------------------------

### DAYOFYEAR Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example demonstrating the DAYOFYEAR function to get the day of the year.

```ppl
source=people
| eval `DAYOFYEAR(DATE('2020-08-26'))` = DAYOFYEAR(DATE('2020-08-26'))
| fields `DAYOFYEAR(DATE('2020-08-26'))`
```

--------------------------------

### DAY_OF_WEEK Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example demonstrating the DAY_OF_WEEK function to get the weekday index.

```ppl
source=people
| eval `DAY_OF_WEEK(DATE('2020-08-26'))` = DAY_OF_WEEK(DATE('2020-08-26'))
| fields `DAY_OF_WEEK(DATE('2020-08-26'))`
```

--------------------------------

### Basic minspan

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example uses the `minspan` parameter to set a minimum width for each bin.

```ppl
source=accounts
| bin age minspan=5
| fields age, account_number
| head 3
```

--------------------------------

### DAYOFWEEK Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example demonstrating the DAYOFWEEK function to get the weekday index.

```ppl
source=people
| eval `DAYOFWEEK(DATE('2020-08-26'))` = DAYOFWEEK(DATE('2020-08-26'))
| fields `DAYOFWEEK(DATE('2020-08-26'))`
```

--------------------------------

### DAYNAME Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example demonstrating the DAYNAME function to get the name of the weekday.

```ppl
source=people
| eval `DAYNAME(DATE('2020-08-26'))` = DAYNAME(DATE('2020-08-26'))
| fields `DAYNAME(DATE('2020-08-26'))`
```

--------------------------------

### Lookup command syntax examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/lookup

Various examples demonstrating the syntax of the lookup command with different options for mapping fields and applying results.

```sql
source = table1 | lookup table2 id
```

```sql
source = table1 | lookup table2 id, name
```

```sql
source = table1 | lookup table2 id as cid, name
```

```sql
source = table1 | lookup table2 id as cid, name replace dept as department
```

```sql
source = table1 | lookup table2 id as cid, name replace dept as department, city as location
```

```sql
source = table1 | lookup table2 id as cid, name append dept as department
```

```sql
source = table1 | lookup table2 id as cid, name append dept as department, city as location
```

```sql
source = table1 | lookup table2 id as cid, name output dept as department
```

```sql
source = table1 | lookup table2 id as cid, name output dept as department, city as location
```

--------------------------------

### CURTIME Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example of using the CURTIME() function to get the current time.

```sql
source=people
| eval `value_1` = CURTIME(), `value_2` = CURTIME()
| fields `value_1`, `value_2`
```

--------------------------------

### CURDATE Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example of using the CURDATE() function to get the current date.

```sql
source=people
| eval `CURDATE()` = CURDATE()
| fields `CURDATE()`
```

--------------------------------

### Example 2: Fetching metadata with a condition and filter

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/describe

This example retrieves columns of the type bigint from the accounts index.

```ppl
describe accounts
| where TYPE_NAME="bigint"
| fields COLUMN_NAME
```

--------------------------------

### NOW Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example demonstrating the usage of the NOW function to get the current date and time.

```sql
source=people
| eval `value_1` = NOW(), `value_2` = NOW()
| fields `value_1`, `value_2`
```

--------------------------------

### last_day() Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example of using the last_day() function to get the last day of a given month.

```ppl
source=people
| eval `last_day('2023-02-06')` = last_day('2023-02-06')
| fields `last_day('2023-02-06')`
```

--------------------------------

### Log volume distribution with bins parameter

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example combines `stats` to calculate volume and then uses `bin` with the `bins` parameter to group the volume into 4 bins.

```ppl
source=otellogs
| stats count() as volume by `resource.attributes.service.name`
| bin volume bins=4
| stats count() as service_count by volume
| sort volume
```

--------------------------------

### Run the CLI with PPL

Source: https://docs.opensearch.org/latest/sql-and-ppl/cli

Launches the CLI and specifies PPL as the query language.

```bash
opensearchsql -l ppl <params>

```

--------------------------------

### EXTRACT Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example demonstrating the EXTRACT function to get the YEAR_MONTH part from a timestamp.

```sql
source=people
| eval `extract(YEAR_MONTH FROM "2023-02-07 10:11:12")` = extract(YEAR_MONTH FROM "2023-02-07 10:11:12")
| fields `extract(YEAR_MONTH FROM "2023-02-07 10:11:12")`
```

--------------------------------

### LAST function example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/aggregations

Example of using the LAST function to get the last non-null firstname by gender.

```ppl
source=accounts
| stats last(firstname) by gender
```

--------------------------------

### streamstats command syntax examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/streamstats

Examples demonstrating the various ways to use the streamstats command, including parameters like window, current, global, reset_before, reset_after, and aggregation functions with by-clause.

```text
source = table | streamstats avg(a)
```

```text
source = table | streamstats current = false avg(a)
```

```text
source = table | streamstats window = 5 sum(b)
```

```text
source = table | streamstats current = false window = 2 max(a)
```

```text
source = table | where a < 50 | streamstats count(c)
```

```text
source = table | streamstats min(c), max(c) by b
```

```text
source = table | streamstats count(c) as count_by by b | where count_by > 1000
```

```text
source = table | streamstats dc(field) as distinct_count
```

```text
source = table | streamstats distinct_count(category) by region
```

```text
source = table | streamstats current=false window=2 global=false avg(a) by b
```

```text
source = table | streamstats window=2 reset_before=a>31 avg(b)
```

```text
source = table | streamstats current=false reset_after=a>31 avg(b) by c
```

--------------------------------

### Explain command in extended mode

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/explain

This example uses the `explain` command in the `extended` mode to show the generated code.

```ppl
explain extended source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country

```

--------------------------------

### FIRST function example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/aggregations

Example of using the FIRST function to get the first non-null firstname by gender.

```ppl
source=accounts
| stats first(firstname) by gender
```

--------------------------------

### Run the CLI tool with default security settings

Source: https://docs.opensearch.org/latest/sql-and-ppl/cli

Connects to an OpenSearch instance with default security settings.

```bash
opensearchsql --username admin --password admin https://localhost:9200

```

--------------------------------

### TO_DAYS Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

An example demonstrating the usage of the TO_DAYS function to get the day number of a given date.

```ppl
source=people
| eval `TO_DAYS(DATE('2008-10-07'))` = TO_DAYS(DATE('2008-10-07'))
| fields `TO_DAYS(DATE('2008-10-07'))`
```

--------------------------------

### Explain API for SQL Query

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/index

Example request to the Explain API to see how an SQL query with a filter is executed against OpenSearch.

```json
POST /_plugins/_sql/_explain\n{\n  "query" : "SELECT firstname, lastname, balance FROM accounts",\n  "filter" : {\n    "range" : {\n      "balance" : {\n        "lt" : 10000\n      }\n    }\n  }\n}
```

--------------------------------

### Basic bins parameter

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example uses the `bins` parameter to divide the data into a specified number of equal-width bins.

```ppl
source=time_test
| bin value bins=5
| fields value
| head 3
```

--------------------------------

### mvindex: Get a single element at index 1

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/collection

Example demonstrating how to get a single element from an array using mvindex with a positive index.

```OpenSearch PPL
source=people
| eval array = array('a', 'b', 'c', 'd', 'e'), result = mvindex(array, 1)
| fields result
| head 1

```

--------------------------------

### Logarithmic span (log10)

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example demonstrates how to use the `log10` span for binning, which creates bins with exponentially increasing sizes.

```ppl
source=accounts
| bin balance span=log10
| fields balance
| head 2
```

--------------------------------

### mvindex: Get the last element using negative indexing

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/collection

Example demonstrating how to get the last element from an array using mvindex with negative indexing.

```OpenSearch PPL
source=people
| eval array = array('a', 'b', 'c', 'd', 'e'), result = mvindex(array, -1)
| fields result
| head 1

```

--------------------------------

### Example 2: Explaining a PPL query in the v3 engine

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/explain

When Apache Calcite is enabled (`plugins.calcite.enabled` is set to `true`), `explain` obtains its logical and physical plan and pushdown information from the v3 engine:

```ppl
explain source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country
```

```json
{
  "calcite": {
    "logical": "LogicalProject(count()=[$1], country=[$0])\n  LogicalAggregate(group=[{1}], count()=[COUNT()])\n    LogicalFilter(condition=[SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7))])\n      CalciteLogicalIndexScan(table=[[OpenSearch, state_country]])\n",
    "physical": "EnumerableCalc(expr#0..1=[{inputs}], count()=[$t1], country=[$t0])\n  CalciteEnumerableIndexScan(table=[[OpenSearch, state_country]], PushDownContext=[[FILTER->SEARCH($1, Sarg['England', 'USA':CHAR(7)]:CHAR(7)), AGGREGATION->rel#53:LogicalAggregate.NONE.[](https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/input=RelSubset#43,group={1},count()=COUNT())], OpenSearchRequestBuilder(sourceBuilder={\"from\":0,\"size\":0,\"timeout\":\"1m\",\"query\":{\"terms\":{\"country\":[\"England\",\"USA\"],\"boost\":1.0}},\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":1000,\"sources\":[{\"country\":{\"terms\":{\"field\":\"country\",\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]},\"aggregations\":{\"count()\":{\"value_count\":{\"field\":\"_index\"}}}}}}, requestedTotalSize=2147483647, pageSize=null, startFrom=0)])\n"
  }
}
```

--------------------------------

### REDUCE Example 1: Summing elements with an initial value

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/collection

This example demonstrates the REDUCE function to sum elements of an array, starting with an initial accumulator value of 10.

```OpenSearch PPL
source=people
| eval array = array(1, -2, 3), result = reduce(array, 10, (acc, x) -> acc + x)
| fields result
| head 1

```

--------------------------------

### Response time distribution from logs

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example demonstrates how to use the bin command to analyze the distribution of response times from logs. It extracts duration in milliseconds, bins it into 100ms intervals, and then counts the occurrences in each bin.

```json
source=otellogs
| rex field=body "(?<duration>\d+)ms"
| bin duration span=100
| stats count() as request_count by duration
| sort duration

```

--------------------------------

### Joining two indexes

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/join

This example demonstrates joining two indexes using the basic `join` syntax.

```ppl
source = state_country
| inner join left=a right=b ON a.name = b.name occupation
| stats avg(salary) by span(age, 10) as age_span, b.country
```

--------------------------------

### Fetching all Prometheus data sources

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/showdatasources

This example demonstrates how to fetch all Prometheus data sources using the `show datasources` command combined with a `where` clause.

```ppl
show datasources
| where CONNECTOR_TYPE='PROMETHEUS'

```

--------------------------------

### UTC_DATE Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Illustrates the usage of the UTC_DATE function to get the current UTC date.

```ppl
source=people
| eval `UTC_DATE()` = UTC_DATE()
| fields `UTC_DATE()`
```

--------------------------------

### Node Stats Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/monitoring

This example shows how to retrieve node-level statistics for the OpenSearch SQL and PPL plugin using a curl command.

```bash
curl -H 'Content-Type: application/json' -X GET localhost:9200/_plugins/_sql/stats
```

--------------------------------

### Example request: Get alert silences

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/read-resources

Fetches alert silences from an Alertmanager data source.

```http
GET /_plugins/_directquery/_resources/my_prometheus/alertmanager/api/v2/silences
```

--------------------------------

### Example request: Get active alerts

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/read-resources

Fetches active alerts from an Alertmanager data source.

```http
GET /_plugins/_directquery/_resources/my_prometheus/alertmanager/api/v2/alerts?active=true&silenced=false
```

--------------------------------

### Daily span

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example demonstrates binning data into 7-day intervals using `span=7day`.

```ppl
source=time_test
| bin @timestamp span=7day
| fields @timestamp, value
| head 3
```

--------------------------------

### Joining with a subsearch

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/join

This example shows how to combine a dataset with a subsearch using the basic `join` syntax.

```ppl
source = state_country as a
| where country = 'USA' OR country = 'England'
| left join ON a.name = b.name [ source = occupation
| where salary > 0
| fields name, country, salary
| sort salary
| head 3 ] as b
| stats avg(salary) by span(age, 10) as age_span, b.country
```

--------------------------------

### Example response: Get active alerts

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/read-resources

Response showing active alerts with their labels, annotations, and status.

```json
[
  {
    "labels": {
      "alertname": "HighMemoryUsage",
      "instance": "server1:9100",
      "severity": "warning"
    },
    "annotations": {
      "summary": "High memory usage detected"
    },
    "startsAt": "2024-01-01T10:00:00.000Z",
    "status": {
      "state": "active"
    }
  }
]
```

--------------------------------

### Multisearch command syntax examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/multisearch

Examples demonstrating the syntax of the multisearch command with different subsearch configurations.

```ppl
| multisearch [search source=table | where condition1] [search source=table | where condition2]
| multisearch [search source=index1 | fields field1, field2] [search source=index2 | fields field1, field2]
| multisearch [search source=table | where status="success"] [search source=table | where status="error"]

```

--------------------------------

### Pattern matching using LIKE

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/replace

This example shows how to use the `LIKE` command with `replace` for pattern matching, as `replace` itself only supports plain string literals.

```ppl
source=accounts
| where LIKE(address, '%Holmes%')
| replace "Holmes" WITH "HOLMES" IN address
| fields address, state, gender, age, city
```

--------------------------------

### WEEKDAY Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Demonstrates how to use the WEEKDAY function to get the weekday index for specific dates.

```sql
source=people
| eval `weekday(DATE('2020-08-26'))` = weekday(DATE('2020-08-26'))
| eval `weekday(DATE('2020-08-27'))` = weekday(DATE('2020-08-27'))
| fields `weekday(DATE('2020-08-26'))`, `weekday(DATE('2020-08-27'))`
```

--------------------------------

### Explain command in cost mode

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/explain

This example uses the `explain` command in the `cost` mode to show plan cost attributes.

```ppl
explain cost source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country

```

--------------------------------

### Example request: Get values for a specific label

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/read-resources

Fetches values for a specific label from a Prometheus data source.

```http
GET /_plugins/_directquery/_resources/my_prometheus/api/v1/label/job/values
```

--------------------------------

### LOCATE Function Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/string

Demonstrates how to use the LOCATE function to find the position of a substring within a string, with and without a starting position.

```ppl
source=people
| eval `LOCATE('world', 'helloworld')` = LOCATE('world', 'helloworld'), `LOCATE('invalid', 'helloworld')` = LOCATE('invalid', 'helloworld'), `LOCATE('world', 'helloworld', 6)` = LOCATE('world', 'helloworld', 6)
| fields `LOCATE('world', 'helloworld')`, `LOCATE('invalid', 'helloworld')`, `LOCATE('world', 'helloworld', 6)
```

--------------------------------

### Second span

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example shows how to bin data into 30-second intervals using `span=30seconds`.

```ppl
source=time_test
| bin @timestamp span=30seconds
| fields @timestamp, value
| head 3
```

--------------------------------

### Index a document with special characters and commas

Source: https://docs.opensearch.org/latest/sql-and-ppl/response-formats

Example of indexing a document where field names start with special characters or contain commas.

```json
PUT /userdata/_doc/1?refresh=true
{
  "+firstname": "-Hattie",
  "=lastname": "@Bond",
  "address": "671 Bristol Street, Dente, TN"
}

```

--------------------------------

### mvindex: Extract elements from the beginning of an array

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/collection

Example demonstrating how to extract elements from the beginning of an array using mvindex with start index 0.

```OpenSearch PPL
source=people
| eval array = array('alex', 'celestino', 'claudia', 'david'), result = mvindex(array, 0, 2)
| fields result
| head 1

```

--------------------------------

### Example 1: Using basic expansion

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/mvexpand

The following query creates an array and expands it into separate rows:

```ppl
source=people
| eval tags = array('error', 'warning', 'info')
| fields tags
| head 1
| mvexpand tags
| fields tags
```

--------------------------------

### mvindex: Extract a range of elements

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/collection

Example demonstrating how to extract a range of elements from an array using mvindex with positive start and end indices.

```OpenSearch PPL
source=people
| eval array = array(1, 2, 3, 4, 5), result = mvindex(array, 1, 3)
| fields result
| head 1

```

--------------------------------

### Default behavior (no parameters)

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example shows the default behavior of the `bin` command when no parameters are specified, which typically creates bins based on the data's range and distribution.

```ppl
source=accounts
| bin age
| fields age, account_number
| head 3
```

--------------------------------

### Example 3: Selecting all severity-related fields with a prefix wildcard

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/fields

Uses a prefix wildcard 'severity*' to select all fields starting with 'severity', such as severityText and severityNumber.

```ppl
source=otellogs
| where severityText IN ('ERROR', 'WARN')
| sort severityNumber, `resource.attributes.service.name`
| fields severity*
| head 3
```

--------------------------------

### Example 3: Get documents that match a condition

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/syntax

A PPL query to retrieve documents from 'otellogs' that match specific severity and service name criteria, and select specific fields.

```ppl
source=otellogs severityText = 'ERROR' AND `resource.attributes.service.name` = 'payment'
| fields severityText, `resource.attributes.service.name`, body

```

--------------------------------

### Extended Syntax Examples

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/join

Illustrates the extended join command syntax, including options for join type, overwrite, max results, and field lists.

```ppl
source = table1 | join type=outer left = l right = r on l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | join type=left left = l right = r where l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | join type=inner max=1 left = l right = r where l.a = r.a table2 | fields l.a, r.a, b, c
source = table1 | join a table2 | fields a, b, c
source = table1 | join a, b table2 | fields a, b, c
source = table1 | join type=outer a b table2 | fields a, b, c
source = table1 | join type=inner max=1 a, b table2 | fields a, b, c
source = table1 | join type=left overwrite=false max=0 a, b [source=table2 | rename d as b] | fields a, b, c
```

--------------------------------

### Minute span

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example demonstrates binning data into 45-minute intervals using `span=45minute`.

```ppl
source=time_test
| bin @timestamp span=45minute
| fields @timestamp, value
| head 3
```

--------------------------------

### Example 4: Get unique field values

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql/basic

Demonstrates how to use the DISTINCT clause in the SELECT statement to retrieve only unique values for one or more specified fields.

```sql
SELECT DISTINCT age
FROM accounts

```

--------------------------------

### mvindex: Extract a range using negative indexing

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/collection

Example demonstrating how to extract a range of elements from an array using mvindex with negative start and end indices.

```OpenSearch PPL
source=people
| eval array = array(1, 2, 3, 4, 5), result = mvindex(array, -3, -1)
| fields result
| head 1

```

--------------------------------

### Example 1: Explaining a PPL query in the v2 engine

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/explain

When Apache Calcite is disabled (`plugins.calcite.enabled` is set to `false`), `explain` obtains its physical plan and pushdown information from the v2 engine:

```ppl
explain source=state_country
| where country = 'USA' OR country = 'England'
| stats count() by country
```

```json
{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": "[count(), country]"
    },
    "children": [
      {
        "name": "OpenSearchIndexScan",
        "description": {
          "request": "OpenSearchQueryRequest(indexName=state_country, sourceBuilder={\"from\":0,\"size\":10000,\"timeout\":\"1m\",\"query\":{\"bool\":{\"should\":[{\"term\":{\"country\":{\"value\":\"USA\",\"boost\":1.0}}},{\"term\":{\"country\":{\"value\":\"England\",\"boost\":1.0}}}],\"adjust_pure_negative\":true,\"boost\":1.0}},\"aggregations\":{\"composite_buckets\":{\"composite\":{\"size\":1000,\"sources\":[{\"country\":{\"terms\":{\"field\":\"country\",\"missing_bucket\":true,\"missing_order\":\"first\",\"order\":\"asc\"}}}]},\"aggregations\":{\"count()\":{\"value_count\":{\"field\":\"_index\"}}}}}}, pitId=null, cursorKeepAlive=null, searchAfter=null, searchResponse=null)"
        },
        "children": []
      }
    ]
  }
}
```

--------------------------------

### Example 1: Query with `in` and `scalar` subsearches

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/subsearch

This query finds suppliers from Canada who supply parts with names starting with 'forest' and have availability quantities greater than half of the total quantity ordered in 1994.

```ppl
source = supplier
| join ON s_nationkey = n_nationkey nation
| where n_name = 'CANADA'
   and s_suppkey in [ /* in subsearch */
     source = partsupp
     | where ps_partkey in [ /* nested in subsearch */
         source = part
         | where like(p_name, 'forest%')
         | fields p_partkey
       ]
       and ps_availqty > [ /* scalar subsearch */
         source = lineitem
         | where l_partkey = ps_partkey
           and l_suppkey = ps_suppkey
           and l_shipdate >= date('1994-01-01')
           and l_shipdate < date_add(date('1994-01-01'), interval 1 year)
         | stats sum(l_quantity) as sum_l_quantity
         | eval half_sum_l_quantity = 0.5 * sum_l_quantity
         | fields half_sum_l_quantity
       ]
     | fields ps_suppkey
   ]
```

--------------------------------

### Syntax

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/explain

The `explain` command has the following syntax:

```ppl
explain <mode> queryStatement
```

--------------------------------

### Logarithmic span with coefficient

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example shows how to use a coefficient with `log10` span to adjust the bin sizes.

```ppl
source=accounts
| bin balance span=2log10
| fields balance
| head 3
```

--------------------------------

### SQL query example

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql/delete

Example of a DELETE statement in SQL.

```sql
DELETE FROM accounts
WHERE age > 30

```

--------------------------------

### Run query from command line

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql/index

Example of running a query from the command line using curl.

```bash
curl -XPOST https://localhost:9200/_plugins/_sql -u 'admin:<custom-admin-password>' -k -H 'Content-Type: application/json' -d '{"query": "SELECT * FROM my-index* LIMIT 50"}'
```

--------------------------------

### Example response

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql-and-ppl-api/data-source-apis/write-resources

An example response when an alert silence is successfully created.

```json
{
  "silenceID": "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
}
```

--------------------------------

### NOT Operator Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/expressions

Example of using the `NOT` operator to negate a condition in a `where` clause.

```ppl
source=accounts
| where not age in (32, 33)
| fields age
```

--------------------------------

### Joining with additional options

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/join

This example uses the extended syntax and optional parameters for more control over the join operation.

```ppl
source = state_country
| join type=inner overwrite=false max=1 name occupation
| stats avg(salary) by span(age, 10) as age_span, country
```

--------------------------------

### Hour span

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/bin

This example shows how to bin data based on hourly intervals using the `span=1h` parameter.

```ppl
source=time_test
| bin @timestamp span=1h
| fields @timestamp, value
| head 3
```

--------------------------------

### Arithmetic Expression Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/expressions

Example of using an arithmetic expression in a `where` clause.

```ppl
source=accounts
| where age > (25 + 5)
| fields age
```

--------------------------------

### Regular identifiers example

Source: https://docs.opensearch.org/latest/sql-and-ppl/identifiers

Example of using regular identifiers in a SQL query.

```sql
SELECT account_number, firstname, lastname FROM accounts;
```

--------------------------------

### Joining using a field list

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/join

This example utilizes the extended syntax and specifies a list of fields for the join criteria.

```ppl
source = state_country
| where country = 'USA' OR country = 'England'
| join type=left overwrite=true name [ source = occupation
| where salary > 0
| fields name, country, salary
| sort salary
| head 3 ]
| stats avg(salary) by span(age, 10) as age_span, country
```

--------------------------------

### Example 1: Retrieve all fields in an index

Source: https://docs.opensearch.org/latest/sql-and-ppl/sql/basic

Demonstrates how to use the asterisk (*) wildcard in the SELECT clause to retrieve all fields from a specified index.

```sql
SELECT *
FROM accounts

```

--------------------------------

### LOCALTIME() Example

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/functions/datetime

Example of using the LOCALTIME() function, a synonym for NOW().

```ppl
source=people
| eval `LOCALTIME()` = LOCALTIME()
| fields `LOCALTIME()`
```

--------------------------------

### Example 2: Lists and nesting

Source: https://docs.opensearch.org/latest/sql-and-ppl/ppl/commands/spath

Demonstrates traversing nested fields and extracting list elements using spath.

```sql
source=structured
| spath input=doc_list output=first_element list{0}
| spath input=doc_list output=all_elements list{}
| spath input=doc_list output=nested nest_out.nest_in
| fields doc_list first_element all_elements nested

```
Tokens:
Raw