# OpenSearch indexes and queries — derived search projections

An OpenSearch index is a **derived, eventually consistent projection** of a DynamoDB table: a PITR export seeds existing items, then DynamoDB Streams and OpenSearch Ingestion (OSIS) keep the projection current. DynamoDB remains the only system of record. DynoStudio provisions and monitors the feed, translates reads to OpenSearch Query DSL, and discloses the consistency and native work on every run.

> **Professional and AWS-only:** The whole OpenSearch surface is Professional. A stage needs a one-time Search binding (⌘K → Connect Search): an OpenSearch domain or Serverless collection endpoint, the OSIS pipeline role, a PITR-export bucket, and the team control table. Endpoint traffic is SigV4-signed, and non-AWS hosts are refused.

## Create and shape the projection

```sql
CREATE OPENSEARCH INDEX "open-orders" ON "Orders"
  PROJECT (orderId, customer, notes, status, total, shipTo, embedding)
  MAPPING (notes SEARCH, status FILTER, shipTo GEO,
           embedding VECTOR (dimension 1536))
  ON INSERT, MODIFY, REMOVE
  WHERE status <> 'ARCHIVED';

DESCRIBE OPENSEARCH INDEX "open-orders";
SHOW OPENSEARCH INDEXES ON "Orders";

ALTER OPENSEARCH INDEX "open-orders"
  SET PROJECT (orderId, customer, notes, status, total, shipTo, embedding, region);
ALTER OPENSEARCH INDEX "open-orders" DROP WHERE;

REINDEX OPENSEARCH INDEX "open-orders";
DROP OPENSEARCH INDEX "open-orders" INCLUDING DOCUMENTS;
```

- **`PROJECT ALL | KEYS | (a, b, …)`** shapes each indexed document; source key attributes are always included.
- **`MAPPING` roles** — `SEARCH` creates analyzed text, `FILTER` creates an exact keyword field, `GEO` creates `geo_point`, and `VECTOR (dimension n)` creates `knn_vector`. A mapped field must also be projected.
- **`ON INSERT, MODIFY, REMOVE`** chooses propagated stream events (all three by default). Omitting `MODIFY` or `REMOVE` deliberately accepts stale-copy or ghost-document risk, and the preview says so.
- **Partial `WHERE`** accepts AND-conjoined field-to-literal comparisons (`=`, `<>`, `>`, `>=`, `<`, `<=`) and `IN (…)`, using string, number, or boolean literals. `OR`, `NOT`, `BETWEEN`, functions, and field-to-field comparisons refuse.

An item that changes out of a partial projection's scope is deleted from the derived index, so the feed does not leave an old in-scope copy behind. Initial export and ingestion failures land in the configured S3 dead-letter location.

## Provisioning and ownership guardrails

- **Preconditions** — source Streams must carry `NEW_IMAGE` or `NEW_AND_OLD_IMAGES`, PITR must be enabled for the seed export, and the OSIS pipeline must reach `ACTIVE` before the registration becomes live.
- **Resume-safe create** — rerunning the same `CREATE` can resume this stage's failed or interrupted registration. A changed definition refuses with the DROP-first fix.
- **No silent adoption** — an existing index or pipeline outside that matching resume refuses by name; DynoStudio never feeds a stranger's resource.
- **VPC destinations** — the Search binding must supply the pipeline subnet and security-group configuration.
- **Team concurrency** — an interleaved teammate change surfaces as a conflict with the `DESCRIBE`, then rerun fix rather than silently overwriting their registration.

## Describe, alter, reindex and drop

- **`DESCRIBE` / `SHOW`** return results-grid rows with the source, registration and OSIS status, events, projection, partial scope, and update time. The result stays exportable and chartable.
- **`ALTER`** accepts one action per statement: `SET PROJECT`, additive `SET MAPPING`, `ADD/DROP EVENT`, or `SET/DROP WHERE`. It changes future ingestion only; widening a projection or scope names the `REINDEX` needed to rewrite history.
- **`REINDEX`** is a true full rebuild: it stops the writer, replaces the physical index empty, then reseeds from a fresh PITR export. It uses a type-the-index-name confirmation, resumes an interrupted rotation, and removes ghost documents.
- **Plain `DROP`** removes the pipeline and registration but leaves the derived documents, so it needs no destructive confirmation. **`INCLUDING DOCUMENTS`** deletes the physical index only after the writer is confirmed gone and uses a type-the-index-name confirmation.

> **Two spellings are recognized but still refuse:** `REPAIR` and `REINDEX … USING GLUE` are not supported execution paths yet. DynoStudio recognizes them only to refuse with a named explanation; use the full `REINDEX OPENSEARCH INDEX` path shown above.

## Query the index

```sql
SELECT orderId, customer, status, total
FROM OPENSEARCH "open-orders"
WHERE notes MATCH 'urgent refund'
  AND status = 'OPEN' AND total >= 100
ORDER BY status ASC LIMIT 50
HIGHLIGHT (notes)
FACET status
```

`FROM OPENSEARCH "index"` and provider-qualified `FROM opensearch."index"` lower to OpenSearch Query DSL. `MATCH` becomes analyzed full-text search; equality, `IN`, ranges, and `BETWEEN` become filter clauses; `HIGHLIGHT` requests matched fragments; `FACET` returns typed term buckets shown in the run footer. `LIMIT` and `OFFSET` map to OpenSearch `size` and `from`; offsets above 10,000 refuse with the narrower-filter/search-after fix.

- **Top-level fields only** — projection, sort, highlight, and facet lists accept top-level field names.
- **String mapping matters** — exact string comparison, `ORDER BY`, and `FACET` require a `FILTER` mapping because dynamically ingested strings are analyzed text. Numeric and boolean filters can work without it.
- **Direct-plane boundaries** — `JOIN`, `GROUP BY`, `HAVING`, set operations, and CTEs do not run inside a single OpenSearch SELECT. Compose a supported bounded operation through the [hybrid query engine](https://dynostudio.dev/docs/dynostudio-hybrid-queries/) instead.
- **Eventually consistent** — search results may lag DynamoDB by seconds. Use an authoritative DynamoDB point read when correctness requires the latest committed item.

## Use cases

### Full-text search with an exact status filter

Find open orders whose notes mention an urgent refund, return highlighted matches, and show status bucket counts.

```sql
SELECT orderId, customer, status, total
FROM OPENSEARCH "open-orders"
WHERE notes MATCH 'urgent refund'
  AND status = 'OPEN' AND total >= 100
ORDER BY status ASC LIMIT 50
HIGHLIGHT (notes)
FACET status
```

_Executes as:_ OpenSearch Query DSL · match notes · term status · range total · highlight notes · terms facet status

- `notes` needs a `SEARCH` mapping. Exact string status filtering, sorting, and faceting need `status FILTER`.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-opensearch-index/#urgent-refunds

### Backfill a widened projection

An ALTER added `region`, so new stream events contain it but historical documents do not. Rebuild the projection from the source of truth.

```sql
REINDEX OPENSEARCH INDEX "open-orders";
```

_Executes as:_ Stop OSIS writer → replace index empty → fresh PITR seed → resume stream ingestion

- The type-the-index-name confirmation protects the physical document replacement.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-opensearch-index/#rebuild-after-widening

Related: [SELECT](https://dynostudio.dev/docs/dynostudio-select/#opensearch-full-text) · [Hybrid and cross-plane queries](https://dynostudio.dev/docs/dynostudio-hybrid-queries/) · [Tables, indexes, backups & replicas](https://dynostudio.dev/docs/dynostudio-partiql-ddl/) · [keyword reference](https://dynostudio.dev/docs/dynostudio-partiql-keywords/).

---
_Source: https://dynostudio.dev/docs/dynostudio-opensearch-index/ · DynoStudio Docs_