# opensearch-search

Part of **OPENSEARCH**

# OpenSearch Query Execution

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Execute Queries | Synchronous | Run and interpret the results of search queries. |
| Interpret Query Results | Synchronous | Understand and process the structure of query results. |
| Get Query Results | Synchronous | Retrieve query results in specified formats. |
| Execute Query | Synchronous | Run general queries using standard query syntax. |
| Execute SQL Query | Synchronous | Run SQL queries as part of query execution operations. |
| Perform Join Query | Synchronous | Execute JOIN operations between tables. |
| Sort Query Results | Synchronous | Apply ORDER BY to sort query output. |
| Configure Query Behavior | Synchronous | Use kvpair clause to adjust query execution parameters. |
| Parse ProtoBuf Results | Synchronous | Process query results returned in ProtoBuf format. |
| Get Query Results in JSON Format | Synchronous | Retrieve and work with JSON-formatted query results. |

## API Calling Patterns

### Authentication
No authentication is required for local or internal OpenSearch Retrieval Engine Edition deployments. Queries are sent directly to the QRS (Query Result Searcher) service endpoint without credentials.

- Header: None required
- Environment variable: Not applicable
- Note: In production Alibaba Cloud environments, authentication may be enforced via network ACLs or reverse proxies, but the core API itself does not require API keys or tokens.

### Service Endpoint
The base URL for query execution follows this pattern:

- `http://<ip>:<port>/sql` — for SQL queries via GET
- `http://<ip>:<port>/` — for general query execution (used by CLI tools)

Common endpoints in examples:
- `http://localhost:39341/`
- `http://ip:port/sql`

Replace `<ip>` and `<port>` with your actual OpenSearch QRS service address.

### Synchronous Query Execution
All query execution in this domain is synchronous. The typical flow is:

1. Construct a query string containing:
   - A `query` parameter with the SQL statement (URL-encoded)
   - An optional `kvpair` clause with semicolon-separated key-value settings
2. Send a GET request to `/sql` (or POST via CLI tools)
3. Receive a complete JSON, string, or binary (ProtoBuf/flatbuffers) response immediately
4. Parse the response based on the `formatType` used

Example URL structure:
```text
http://ip:port/sql?query=SELECT+...+FROM+table&&kvpair=formatType:json;trace:INFO
```

Note: The double ampersand (`&&`) separates the query from kvpair, and spaces must be URL-encoded.

## Parameter Reference

### Query Execution Parameters

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| query | string | Yes | — | Must be URL-encoded | The SQL query to execute |
| kvpair | string | No | — | Format: `key1:value1;key2:value2`; no spaces | Additional configuration parameters |

### Result Format Parameters (via kvpair)

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| formatType | string | No | string | one of: string, json, full_json, flatbuffers | Specifies the result format |
| searchInfo | boolean | No | false | true or false | Includes detailed search metrics |
| resultReadable | boolean | No | false | true or false | Adds line breaks for readable JSON |
| sqlPlan | boolean | No | false | true or false | Returns SQL execution plan info |
| trace | string | No | DISABLE | one of: DISABLE, FATAL, ERROR, WARN, INFO, DEBUG, TRACE1, TRACE2, TRACE3, SCHEDULE, NOTSET | Sets frontend trace verbosity |
| timeout | integer | No | Config-dependent | ulimit | Query timeout in milliseconds |

### Sorting and Pagination (SQL-level)

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| LIMIT N | integer | Required with ORDER BY | — | max ~10000 | Maximum rows to return |
| OFFSET M | integer | No | — | non-negative integer | Rows to skip for pagination |
| orderByItem | string | No | — | — | Field(s) to sort by (comma-separated) |
| ASC / DESC | string | No | ASC | one of: ASC, DESC | Sort direction |

### Join and Optimization (via kvpair)

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|--------|--------|------------|-------------|
| parallel | integer | No | 1 | range 1-16 | Enables parallel optimization |
| parallelTables | string | No | "" | — | Tables for parallel join (pipe-separated) |
| iquan.optimizer.join.condition.check | boolean | No | true | true / false | Require hash fields in JOIN |
| iquan.optimizer.force.hash.join | boolean | No | false | true / false | Force hash join conversion |
| dynamic_params | array | No | N/A | Two-dimensional arrays | Values for SQL placeholders |

## Code Examples

### Basic Query Execution - Bash - All Regions

```bash
curl "http://ip:port/sql?query=SELECT+brand%2C+COUNT%28%2A%29+FROM+phone+GROUP+BY+%28brand%29&&kvpair=trace:INFO;formatType:json"
```

### Retrieve Results in JSON Format - Bash - All Regions

```bash
./search.sh -s "select nid, price, brand, size from phone&&kvpair=formatType:json"
```

### Parse ProtoBuf Results - Python - All Regions

```python
from isearch.common import PBResult_pb2

result = PBResult_pb2.PBResult()
result.ParseFromString(<response_bytes>)

# Access hits
hits = result.hits
print("Hits:", hits.numhits, "of", hits.totalHits)
for hit in hits.hit:
    print("Cluster:", hit.clusterName, "DocID:", hit.docid)

# Check for errors
for err in result.errorResults:
    print(f"Error {err.errorCode}: {err.errorDescription}")
```

### Perform a Left Join - SQL - All Regions

```sql
SELECT
  t1.id, t2.id
FROM
  tj_shop AS t1
LEFT JOIN
  tj_item AS t2
ON
  t1.id = t2.id
```

### Sort with Pagination - SQL - All Regions

```sql
SELECT nid, brand, price, size FROM phone ORDER BY price DESC LIMIT 10 OFFSET 10
```

### Configure Advanced Query Behavior - JavaScript - All Regions

```javascript
query=SELECT brand, COUNT(*) FROM phone &&kvpair=trace:INFO;formatType:full_json
```

### Parse ProtoBuf Results - Java - All Regions

```java
import isearch.common.PBResultProto.PBResult;

// Parse binary response into PBResult
PBResult result = PBResult.parseFrom(<response_bytes>);

// Access hits
PBResult.PBHits hits = result.getHits();
int numHits = hits.getNumhits();
for (PBResult.PBHit hit : hits.getHitList()) {
    String clusterName = hit.getClusterName();
    // Access attributes, summary, sort values, etc.
}

// Check for errors
for (PBResult.PBErrorResult err : result.getErrorResultsList()) {
    System.out.println("Error " + err.getErrorCode() + ": " + err.getErrorDescription());
}
```

## Response Format

```json
{
  "error_info": "{\"Error\": ERROR_NONE}",
  "format_type": "json",
  "row_count": 10,
  "search_info": "scanInfos { kernelName: \"ScanKernel\" nodeName: \"0_0\" tableName: \"phone\" hashKey: 243934**** parallelNum: 1 totalOutputCount: 10 totalScanCount: 10 totalUseTime: 81 totalSeekTime: 28 totalEvaluateTime: 11 totalOutputTime: 40 totalComputeTimes: 1 }",
  "sql_result": "{\"column_name\":[\"nid\",\"price\",\"brand\",\"size\"],\"column_type\":[\"uint64\",\"double\",\"multi_char\",\"double\"],\"data\":[[1,3599,\"Huawei\",5.9],[2,4388,\"Huawei\",5.5],[3,899,\"Xiaomi\",5],[4,2999,\"OPPO\",5.5],[5,1299,\"Meizu\",5.5],[6,169,\"Nokia\",1.4],[7,3599,\"Apple\",4.7],[8,5998,\"Apple\",5.5],[9,4298,\"Apple\",4.7],[10,5688,\"Samsung\",5.6]]}",
  "total_time": 0.024,
  "trace": []
}
```

**Key Fields**:
- `format_type` — Indicates the result format (json, string, etc.)
- `row_count` — Number of result rows returned
- `sql_result` — Contains the actual query data as a JSON-encoded string
- `total_time` — Total query execution time in seconds
- `search_info` — Performance metrics for scan, sort, and other operators
- `error_info` — Error status (e.g., `ERROR_NONE` for success)

### JSON Format Response Structure (Alternative)

```json
{
    "result": {
        "searchtime": 0.001,
        "totalHits": 10,
        "numHits": 10,
        "coveredPercent": 100.0,
        "items": [
            {
                "fields": {
                    "normal_key": "normal_value",
                    "multi_key": "valuetvalue1"
                },
                "attribute": {
                    "int_key": [1, 2],
                    "double_key": [1.1, 2.1],
                    "string_key": ["1", "2"]
                },
                "sortExprValues": ["1.001", "1.002"]
            }
        ],
        "facet": [
            {
                "key": "expr1",
                "items": [
                    { "value": "key1", "sum": "4444", "count": "123" }
                ]
            }
        ]
    },
    "errors": [
        { "code": 1, "message": "error" }
    ],
    "tracer": "trace info\n"
}
```

**Key Fields**:
- `result.searchtime` — Query execution time in seconds
- `result.totalHits` — Total matching documents (before limit)
- `result.numHits` — Actual number of returned items
- `result.items[].fields` — Document field values
- `result.items[].attribute` — Typed attribute values (arrays for multi-value)
- `result.facet[].items[].count` — Aggregation counts
- `errors[].code` — Error code if any occurred

## Error Handling

| Error Code | Description | Recommended Action |
|------------|-------------|-------------------|
| 400 | Bad Request — caused by malformed query or invalid kvpair format (e.g., spaces in kvpair). | Ensure the query is properly URL-encoded and kvpair uses `key:value;key:value` without spaces. |
| 500 | Internal Server Error — server-side issue during query execution. | Check server logs; retry with simplified query or reduced scope. |
| ERROR_NONE | Indicates that the query executed successfully. No error occurred. | Proceed to parse `sql_result` or `result` fields. |
| 1 | Generic error code indicating an unspecified error occurred during query execution. Check the message field for details. | Inspect the `message` or `error_info` field for diagnostic details. |

## Common Questions

Q: How do I specify the output format for my query results?
A: Use the `formatType` parameter in the `kvpair` clause. Valid values are `string`, `json`, `full_json`, and `flatbuffers`. Example: `&&kvpair=formatType:json`.

Q: Why am I getting a 400 error when sending a query?
A: This usually means your query or kvpair contains unescaped spaces or invalid syntax. Ensure the entire query string is URL-encoded and that kvpair uses semicolons (`;`) without spaces: `key1:value1;key2:value2`.

Q: Do I need to include LIMIT when using ORDER BY?
A: Yes. OpenSearch requires a `LIMIT` clause whenever `ORDER BY` is used. The maximum limit is typically 10,000 rows.

Q: How can I debug slow queries?
A: Enable detailed tracing by adding `trace:INFO` or `trace:DEBUG` to your kvpair, and set `searchInfo:true` to get operator-level performance metrics in the response.

Q: Can I use JOINs between tables?
A: Yes. OpenSearch supports `LEFT JOIN`, `INNER JOIN`, `SEMI-JOIN`, and `ANTI-JOIN`. For optimal performance, ensure joined fields are hash-indexed and consider enabling `iquan.optimizer.force.hash.join:true`.

## Pricing & Billing

### Billing Model
Billing is based on a per-request model. Each query execution (including failed ones) counts as one billable request.

### Price Reference

| Tier | Input Price | Output Price |
|------|-------------|--------------|
| default | 0.001 / | 0.002 / |

### Free Tier
Monthly free quota of 1,000 requests.

### Usage Limits
Maximum query rate of 100 queries per second (QPS).

### Billing Notes
Billing applies to all query attempts, including those that result in errors (e.g., 400 or 500 responses).