Indexing and searching in OpenSearch: mapping, DSL, PPL and SQL

1. Declaring your indexes #

Template and mapping.

An index template #

Declare types before the first write: an existing field type cannot change without reindexing.

curl -sk -u 'admin:Str0ng!Passw0rd-2026' -H 'Content-Type: application/json' -X PUT https://localhost:9200/_index_template/logs_app -d '{
  "index_patterns": ["logs-app-*"],
  "template": {
    "settings": {"number_of_shards": 1, "number_of_replicas": 0},
    "mappings": {"properties": {
      "@timestamp": {"type": "date"},
      "level": {"type": "keyword"},
      "service": {"type": "keyword"},
      "message": {"type": "text"},
      "duree_ms": {"type": "integer"}
    }}
  }
}'

2. Indexing #

The bulk API.

Writing in batches #

cat > lot.ndjson <<'EOF'
{"index":{"_index":"logs-app-000001"}}
{"@timestamp":"2026-11-04T10:00:00Z","level":"ERROR","service":"paiement","message":"timeout vers la banque utilisateur=alice","duree_ms":3100}
{"index":{"_index":"logs-app-000001"}}
{"@timestamp":"2026-11-04T10:00:01Z","level":"INFO","service":"paiement","message":"paiement accepté","duree_ms":120}
{"index":{"_index":"logs-app-000001"}}
{"@timestamp":"2026-11-04T10:00:02Z","level":"INFO","service":"panier","message":"article ajouté","duree_ms":40}
EOF
curl -sk -u 'admin:Str0ng!Passw0rd-2026' -H 'Content-Type: application/x-ndjson' \
  -X POST 'https://localhost:9200/_bulk?refresh=true' --data-binary @lot.ndjson

Best practice: always use _bulk for large volumes, and read "errors" in the response: the HTTP code stays 200 even when documents are refused. Avoid refresh=true in production; here it lets you read the result immediately.

3. Searching #

Query DSL, aggregations, PPL and SQL.

Search #

In a bool, must counts towards the score, while filter only says yes or no, and is cached. Put in filter everything that does not need relevance.

curl -sk -u 'admin:Str0ng!Passw0rd-2026' -H 'Content-Type: application/json' -X POST 'https://localhost:9200/logs-app-*/_search' -d '{
  "query": {"bool": {
    "must":   [{"match": {"message": "banque"}}],
    "filter": [{"term": {"service": "paiement"}}]
  }}
}'

Aggregations #

curl -sk -u 'admin:Str0ng!Passw0rd-2026' -H 'Content-Type: application/json' -X POST 'https://localhost:9200/logs-app-*/_search' -d '{
  "size": 0,
  "aggs": {"par_service": {
    "terms": {"field": "service"},
    "aggs": {"p95": {"percentiles": {"field": "duree_ms", "percents": [95]}}}
  }}
}'

Result of our test: paiement has 2 documents (p95 of 3100 ms) and panier 1 (p95 of 40 ms).

PPL and SQL #

To explore logs, PPL chains operations with |, like a shell.

curl -sk -u 'admin:Str0ng!Passw0rd-2026' -H 'Content-Type: application/json' -X POST https://localhost:9200/_plugins/_ppl \
  -d '{"query":"source=logs-app-* | where service=\"paiement\" | stats count() by level"}'

curl -sk -u 'admin:Str0ng!Passw0rd-2026' -H 'Content-Type: application/json' -X POST https://localhost:9200/_plugins/_sql \
  -d '{"query":"SELECT service, COUNT(*) n FROM logs-app-000001 GROUP BY service"}'