Elasticsearch Mapping and Query Basics

Field	Value
Type	Skill Resource
Source	~/.copilot/skills/database/references/elasticsearch.md
Description	Not specified

Source Content

Elasticsearch Mapping and Query Basics

This is a lighter reference for Elasticsearch fundamentals; Postgres is the primary engine in this skill. Expand this reference as search and analytics workloads appear.

Index and Mapping Fundamentals

An Elasticsearch index is like a table; a mapping defines the schema for fields.

Create an Index with Mapping

PUT /products
{
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"  -- Not analyzed; exact match only
      },
      "name": {
        "type": "text",
        "analyzer": "standard"  -- Tokenized full-text
      },
      "description": {
        "type": "text",
        "analyzer": "standard"
      },
      "category": {
        "type": "keyword"  -- Faceting, exact filters
      },
      "price": {
        "type": "scaled_float",
        "scaling_factor": 100  -- Store as integer for efficiency
      },
      "tags": {
        "type": "keyword"  -- Array of exact values
      },
      "created_at": {
        "type": "date"
      },
      "updated_at": {
        "type": "date"
      }
    }
  }
}

Field Types

Type	Use Case	Example
keyword	Exact matches, faceting, filtering, sorting	category, status, id
text	Full-text search, analyzed	title, description, content
integer	Numeric filtering, aggregation	quantity, age
long	Large integers	row counts, IDs
scaled_float	Decimals with fixed precision	price, rating
date	Timestamps and date ranges	created_at, updated_at
object	Nested structure (flat)	metadata, user details
nested	Arrays of objects with independent fields	comments, line items
geo_point	Geographic coordinates	latitude, longitude

Indexing Strategy

Keyword Analyzer (No Tokenization)

Use for exact matches, faceting, and sorting:

{
  "mappings": {
    "properties": {
      "status": {
        "type": "keyword"  -- Values: "active", "inactive", "pending"
      },
      "region": {
        "type": "keyword"  -- Facet by region
      }
    }
  }
}

Text Analyzer (Tokenized)

Use for full-text search:

{
  "mappings": {
    "properties": {
      "title": {
        "type": "text",
        "analyzer": "standard",
        "fields": {
          "keyword": {
            "type": "keyword"  -- Also store exact value for sorting
          }
        }
      }
    }
  }
}

Query the analyzed field for search; the .keyword subfield for sorting.

Custom Analyzers

PUT /articles
{
  "settings": {
    "analysis": {
      "analyzer": {
        "english_analyzer": {
          "type": "standard",
          "stopwords": "_english_"  -- Remove common English words
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "content": {
        "type": "text",
        "analyzer": "english_analyzer"
      }
    }
  }
}

Query Types

Match Query (Full-Text Search)

GET /products/_search
{
  "query": {
    "match": {
      "description": "fast database"  -- Tokenized, OR by default
    }
  }
}

Match Phrase (Exact Phrase)

GET /products/_search
{
  "query": {
    "match_phrase": {
      "title": "high performance database"
    }
  }
}

Term Query (Exact Keyword Match)

GET /products/_search
{
  "query": {
    "term": {
      "category": "electronics"
    }
  }
}

Range Query

GET /products/_search
{
  "query": {
    "range": {
      "price": {
        "gte": 10,
        "lte": 100
      }
    }
  }
}

Bool Query (AND, OR, NOT)

GET /products/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "name": "database"
          }
        }
      ],
      "filter": [
        {
          "term": {
            "category": "software"
          }
        },
        {
          "range": {
            "price": {
              "gte": 50
            }
          }
        }
      ],
      "must_not": [
        {
          "term": {
            "status": "discontinued"
          }
        }
      ]
    }
  }
}

Aggregations (Analytics)

Terms Aggregation (Faceting)

GET /products/_search
{
  "size": 0,  -- No documents, just aggregations
  "aggs": {
    "categories": {
      "terms": {
        "field": "category.keyword",
        "size": 20
      }
    }
  }
}

Response:

{
  "aggregations": {
    "categories": {
      "buckets": [
        {
          "key": "electronics",
          "doc_count": 1245
        },
        {
          "key": "software",
          "doc_count": 892
        }
      ]
    }
  }
}

Date Histogram Aggregation

GET /products/_search
{
  "size": 0,
  "aggs": {
    "sales_by_month": {
      "date_histogram": {
        "field": "created_at",
        "interval": "month"
      }
    }
  }
}

Nested Aggregations

GET /products/_search
{
  "size": 0,
  "aggs": {
    "by_category": {
      "terms": {
        "field": "category.keyword",
        "size": 20
      },
      "aggs": {
        "avg_price": {
          "avg": {
            "field": "price"
          }
        },
        "price_range": {
          "range": {
            "field": "price",
            "ranges": [
              {
                "to": 50
              },
              {
                "from": 50,
                "to": 200
              },
              {
                "from": 200
              }
            ]
          }
        }
      }
    }
  }
}

Document Ingestion

Bulk Indexing

curl -s -X POST http://localhost:9200/_bulk \
  -H 'Content-Type: application/x-ndjson' \
  -d '
{ "index": { "_index": "products", "_id": "1" } }
{ "name": "Database 101", "price": 29.99, "category": "books" }
{ "index": { "_index": "products", "_id": "2" } }
{ "name": "SQL Tuning", "price": 39.99, "category": "books" }
'

Single Document Index

POST /products/_doc
{
  "name": "High Performance Database",
  "price": 49.99,
  "category": "books",
  "tags": ["database", "performance", "sql"],
  "created_at": "2024-01-15T10:00:00Z"
}

Performance Tips

Index Refresh Interval

-- Slow down refreshes during bulk indexing (faster writes)
PUT /products/_settings
{
  "refresh_interval": "30s"  -- Default 1s
}

-- Re-enable after bulk import
PUT /products/_settings
{
  "refresh_interval": "1s"
}

Mapping Best Practices

1. Use keyword for faceting, text for search.
2. Add .keyword subfield to text fields you’ll sort on.
3. Set enabled: false on fields you don’t search:

{
  "mappings": {
    "properties": {
      "internal_id": {
        "type": "keyword",
        "enabled": false  -- Not indexed, saves space
      }
    }
  }
}

4. Use copy_to to combine fields for search:

{
  "mappings": {
    "properties": {
      "title": {
        "type": "text",
        "copy_to": "full_text"
      },
      "description": {
        "type": "text",
        "copy_to": "full_text"
      },
      "full_text": {
        "type": "text"
      }
    }
  }
}

Monitor Index Health

# Check index size
curl -s http://localhost:9200/_cat/indices?v

# Get mapping
curl -s http://localhost:9200/products/_mapping | jq .

When to Use Elasticsearch

• Full-text search across large text fields (millions of documents).
• Faceted search and analytics (count by category, date histogram, etc.).
• Real-time aggregations (top products, trending topics).
• Time-series data (logs, metrics, events).

Don’t use Elasticsearch for:

• Transactional consistency (use Postgres).
• Primary data storage (use a relational database).
• Complex JOINs (use Postgres).
• Small datasets (overhead not worth it).

References

• Elasticsearch Documentation
• Elasticsearch Mapping Guide
• Query DSL