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.

Create and shape the projection

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.

Query the index

SELECT orderId, customer, status, totalFROM OPENSEARCH "open-orders"WHERE notes MATCH 'urgent refund'  AND status = 'OPEN' AND total >= 100ORDER BY status ASC LIMIT 50HIGHLIGHT (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 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

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

REINDEX OPENSEARCH INDEX "open-orders";

Related: SELECT · Hybrid and cross-plane queries · Tables, indexes, backups & replicas · keyword reference.