---
title: OpenSearchSQLRetriever
id: opensearchsqlretriever
slug: /opensearchsqlretriever
description: Executes raw OpenSearch SQL queries against an OpenSearch Document Store and returns the raw JSON response.
---

# OpenSearchSQLRetriever

Executes raw OpenSearch SQL queries against an OpenSearch Document Store and returns the raw JSON response.

|                                         |                                                                                                  |
| --------------------------------------- | ------------------------------------------------------------------------------------------------ |
| **Most common position in a pipeline**  | Standalone, or anywhere you need to fetch metadata, aggregations, or other structured data        |
| **Mandatory init variables**            | `document_store`: An instance of `OpenSearchDocumentStore`                                        |
| **Mandatory run variables**             | `query`: An OpenSearch SQL query string                                                           |
| **Output variables**                    | `result`: A dictionary with the raw JSON response from the OpenSearch SQL API                     |
| **API reference**                       | [OpenSearch](https://docs.haystack.deepset.ai/reference/integrations-opensearch)                  |
| **GitHub link**                         | https://github.com/deepset-ai/haystack-core-integrations/tree/main/integrations/opensearch        |
| **Package name**                        | `opensearch-haystack`                                                                             |

## Overview

`OpenSearchSQLRetriever` lets you run [OpenSearch SQL](https://opensearch.org/docs/latest/search-plugins/sql/index/) queries directly against an `OpenSearchDocumentStore`. Instead of matching a query against documents like the `OpenSearchBM25Retriever` or `OpenSearchEmbeddingRetriever`, it executes a SQL statement and returns the **raw JSON response** from the OpenSearch SQL API.

This is useful when you need structured access to your index at runtime, for example to fetch specific fields, filter on metadata, or compute aggregations such as counts and averages.

Unlike the other OpenSearch retrievers, this component does not return a list of `Document` objects. The output is a single `result` dictionary, where `result["result"]` holds the raw OpenSearch response. Depending on the query:

- For regular queries, `result["result"]["hits"]["hits"]` contains the matching documents.
- For aggregate queries, `result["result"]["aggregations"]` contains the aggregations.

The component accepts two optional parameters at initialization:

- `raise_on_failure`: if `True` (the default), an exception is raised when the SQL API call fails. If `False`, the error is logged as a warning and the result is empty.
- `fetch_size`: the number of results to fetch per page. If not set, the default fetch size configured in OpenSearch is used.

## Installation

Install OpenSearch and then start an instance.

If you have Docker set up, we recommend pulling the Docker image and running it.

```bash
docker pull opensearchproject/opensearch:2
docker run -p 9200:9200 -p 9600:9600 -e "discovery.type=single-node" -e "OPENSEARCH_INITIAL_ADMIN_PASSWORD=<custom-admin-password>" opensearchproject/opensearch:2
```

As an alternative, you can go to [OpenSearch integration GitHub](https://github.com/deepset-ai/haystack-core-integrations/tree/main/integrations/opensearch) and start a Docker container running OpenSearch using the provided `docker-compose.yml`:

```bash
docker compose up
```

Once you have a running OpenSearch instance, install the `opensearch-haystack` integration:

```bash
pip install opensearch-haystack
```

## Usage

### On its own

Write a few documents to an index, then run a SQL query against it. The example below selects the `content` field from the index and reads the returned hits:

```python
from haystack import Document
from haystack_integrations.components.retrievers.opensearch import (
    OpenSearchSQLRetriever,
)
from haystack_integrations.document_stores.opensearch import (
    OpenSearchDocumentStore,
)
from haystack.document_stores.types import DuplicatePolicy

document_store = OpenSearchDocumentStore(hosts="http://localhost:9200", index="my_index")

documents = [
    Document(content="There are over 7,000 languages spoken around the world today."),
    Document(
        content="Elephants have been observed to behave in a way that indicates a high level of self-awareness, such as recognizing themselves in mirrors.",
    ),
    Document(
        content="In certain parts of the world, like the Maldives, Puerto Rico, and San Diego, you can witness the phenomenon of bioluminescent waves.",
    ),
]

# DuplicatePolicy.SKIP is optional, but useful to run the script multiple times without throwing errors
document_store.write_documents(documents=documents, policy=DuplicatePolicy.SKIP)

retriever = OpenSearchSQLRetriever(document_store=document_store)
output = retriever.run(query="SELECT content FROM my_index LIMIT 10")

result = output["result"]
# Regular queries return matching documents under hits
for hit in result["hits"]["hits"]:
    print(hit["_source"])
```

### Running an aggregation query

Because the component returns the raw SQL response, you can use it for aggregations that the document-based retrievers don't support, such as counting documents:

```python
retriever = OpenSearchSQLRetriever(document_store=document_store)
output = retriever.run(query="SELECT COUNT(*) AS doc_count FROM my_index")

result = output["result"]
# Aggregate queries expose their results under aggregations
print(result["aggregations"])
```

To avoid raising an exception on a malformed or failing query, initialize the component with `raise_on_failure=False`. In that case, a failed query logs a warning and returns an empty result instead.