Elasticsearch Queries

  1. Create indices
curl -XPUT 'localhost:9200/twitter?pretty' -H 'Content-Type: application/json' -d'
{
 "settings" : {
 "index" : {
 "number_of_shards" : 3,
 "number_of_replicas" : 2
 }
 }
}
'

2. Search

curl -XGET 'localhost:9200/sw/_search?pretty' -H 'Content-Type: application/json' -d'
{
 "query": { "match_all": {} },
 "_source": ["gender", "height"]
}
'</pre>
3. Creating index and adding documents to it
<pre>curl -XPUT 'localhost:9200/my_index?pretty' -H 'Content-Type: application/json' -d'
{
 "mappings": {
 "my_type": {
 "properties": {
 "user": {
 "type": "nested"
 }
 }
 }
 }
}
'
curl -XPUT 'localhost:9200/my_index/my_type/1?pretty' -H 'Content-Type: application/json' -d'
{
 "group" : "fans",
 "user" : [
 {
 "first" : "John",
 "last" : "Smith"
 },
 {
 "first" : "Alice",
 "last" : "White"
 }
 ]
}
'

4. Must match

curl -XGET 'localhost:9200/my_index/_search?pretty' -H 'Content-Type: application/json' -d'
{
 "query": {
 "nested": {
 "path": "user",
 "query": {
 "bool": {
 "must": [
 { "match": { "user.first": "Alice" }},
 { "match": { "user.last": "Smith" }}
 ]
 }
 }
 }
 }
}
'

5. Highlight

curl -XGET 'localhost:9200/my_index/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query": {
    "nested": {
      "path": "user",
      "query": {
        "bool": {
          "must": [
            { "match": { "user.first": "Alice" }},
            { "match": { "user.last":  "White" }}
          ]
        }
      },
      "inner_hits": {
        "highlight": {
          "fields": {
            "user.first": {}
          }
        }
      }
    }
  }
}
'

6. To get all records:
curl -XGET ‘localhost:9200//_search?size=100&pretty=true’ -d ”

7. Match all


curl -XGET 'localhost:9200/foo/_search?size=NO_OF_RESULTS' -d '
{
"query" : {
 "match_all" : {}
 }
}'

8. This example does a match_all and returns documents 11 through 20


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
"query": { "match_all": {} },
"from": 10,
"size": 10
}
'

9. This example does a match_all and sorts the results by account balance in descending order and returns the top 10 (default size) documents


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
"query": { "match_all": {} },
"sort": { "balance": { "order": "desc" } }
}
'

10. This example shows how to return two fields, account_number and balance (inside of _source), from the search


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
"query": { "match_all": {} },
"_source": ["account_number", "balance"]
}
'

11. This example returns the account numbered 20


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
"query": { "match": { "account_number": 20 } }
}
'

12. This example returns all accounts containing the term “mill” in the address


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
"query": { "match": { "address": "mill" } }
}
'

13. This example returns all accounts containing the term “mill” or “lane” in the address


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
"query": { "match": { "address": "mill lane" } }
}
'

14. This example is a variant of match (match_phrase) that returns all accounts containing the phrase “mill lane” in the address


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
"query": { "match_phrase": { "address": "mill lane" } }
}
'

15. This example composes two match queries and returns all accounts containing “mill” and “lane” in the address


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query": {
    "bool": {
      "must": [
        { "match": { "address": "mill" } },
        { "match": { "address": "lane" } }
      ]
    }
  }
}
'

16. In contrast, this example composes two match queries and returns all accounts containing “mill” or “lane” in the address


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query": {
    "bool": {
      "should": [
        { "match": { "address": "mill" } },
        { "match": { "address": "lane" } }
      ]
    }
  }
}
'

17. This example returns all accounts of anybody who is 40 years old but doesn’t live in ID

curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query": {
    "bool": {
      "must": [
        { "match": { "age": "40" } }
      ],
      "must_not": [
        { "match": { "state": "ID" } }
      ]
    }
  }
}
'

18. This example uses a bool query to return all accounts with balances between 20000 and 30000


curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query": {
    "bool": {
      "must": { "match_all": {} },
      "filter": {
        "range": {
          "balance": {
            "gte": 20000,
            "lte": 30000
          }
        }
      }
    }
  }
}
'

19. To start with, this example groups all the accounts by state, and then returns the top 10 (default) states sorted by count descending (also default)

curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "size": 0,
  "aggs": {
    "group_by_state": {
      "terms": {
        "field": "state.keyword"
      }
    }
  }
}
'

20. Building on the previous aggregation, let’s now sort on the average balance in descending order

curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "size": 0,
  "aggs": {
    "group_by_state": {
      "terms": {
        "field": "state.keyword",
        "order": {
          "average_balance": "desc"
        }
      },
      "aggs": {
        "average_balance": {
          "avg": {
            "field": "balance"
          }
        }
      }
    }
  }
}
'

21. This example demonstrates how we can group by age brackets (ages 20-29, 30-39, and 40-49), then by gender, and then finally get the average account balance, per age bracket, per gender

curl -XGET 'localhost:9200/bank/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "size": 0,
  "aggs": {
    "group_by_age": {
      "range": {
        "field": "age",
        "ranges": [
          {
            "from": 20,
            "to": 30
          },
          {
            "from": 30,
            "to": 40
          },
          {
            "from": 40,
            "to": 50
          }
        ]
      },
      "aggs": {
        "group_by_gender": {
          "terms": {
            "field": "gender.keyword"
          },
          "aggs": {
            "average_balance": {
              "avg": {
                "field": "balance"
              }
            }
          }
        }
      }
    }
  }
}
'

22. Assuming the data consists of documents representing exams grades (between 0 and 100) of students we can average their scores with

curl -XPOST 'localhost:9200/exams/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "avg_grade" : { "avg" : { "field" : "grade" } }
    }
}
'

23. Multiply current marks with 1.2 then get the aggregate

curl -XPOST 'localhost:9200/exams/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "avg_corrected_grade" : {
            "avg" : {
                "field" : "grade",
                "script" : {
                    "lang": "painless",
                    "inline": "_value * params.correction",
                    "params" : {
                        "correction" : 1.2
                    }
                }
            }
        }
    }
}
'

24. Documents without a value in the grade field will fall into the same bucket as documents that have the value 10

curl -XPOST 'localhost:9200/exams/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "grade_avg" : {
            "avg" : {
                "field" : "grade",
                "missing": 10
            }
        }
    }
}
'

25. Type count for the balance

curl -XPOST 'localhost:9200/bank/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "type_count" : {
            "cardinality" : {
                "field" : "balance"
            }
        }
    }
}
'

26. Use of inline painless script for adding promoted value to type value

curl -XPOST 'localhost:9200/bank/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "type_promoted_count" : {
            "cardinality" : {
                "script": {
                    "lang": "painless",
                    "inline": "doc[\u0027type\u0027].value + \u0027 \u0027 + doc[\u0027promoted\u0027].value"
                }
            }
        }
    }
}
'

27. Extended stats for balance

curl -XPOST 'localhost:9200/bank/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "grades_stats" : { "extended_stats" : { "field" : "balance" } }
    }
}
'

28. Geopoint and geo centroid example

curl -XPUT 'localhost:9200/museums' -H 'Content-Type: application/json' -d'
{
    "mappings": {
        "doc": {
            "properties": {
                "location": {
                    "type": "geo_point"
                }
            }
        }
    }
}
'

curl -XPOST 'localhost:9200/museums/doc/_bulk?refresh' -H 'Content-Type: application/json' -d'
{"index":{"_id":1}}
{"location": "52.374081,4.912350", "name": "NEMO Science Museum"}
{"index":{"_id":2}}
{"location": "52.369219,4.901618", "name": "Museum Het Rembrandthuis"}
{"index":{"_id":3}}
{"location": "52.371667,4.914722", "name": "Nederlands Scheepvaartmuseum"}
{"index":{"_id":4}}
{"location": "51.222900,4.405200", "name": "Letterenhuis"}
{"index":{"_id":5}}
{"location": "48.861111,2.336389", "name": "Musée du Louvre"}
{"index":{"_id":6}}
{"location": "48.860000,2.327000", "name": "Musée dOrsay"}'

curl -XPOST 'localhost:9200/museums/_search?size=0' -H 'Content-Type: application/json' -d'
{
    "query" : {
        "match" : { "name" : "musée" }
    },
    "aggs" : {
        "viewport" : {
            "geo_bounds" : {
                "field" : "location",
                "wrap_longitude" : true
            }
        }
    }
}
'

curl -XPOST 'localhost:9200/museums/_search?size=0' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "centroid" : {
            "geo_centroid" : {
                "field" : "location"
            }
        }
    }
}
'

curl -XPOST 'localhost:9200/museums/_search?size=0' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "cities" : {
            "terms" : { "field" : "city.keyword" },
            "aggs" : {
                "centroid" : {
                    "geo_centroid" : { "field" : "location" }
                }
            }
        }
    }
}
'

29. Max balance

curl -XPOST 'localhost:9200/bank/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "max_price" : { "max" : { "field" : "balance" } }
    }
}
'

30. Min balance

curl -XPOST 'localhost:9200/sales/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs" : {
        "min_price" : { "min" : { "field" : "price" } }
    }
}
'

31. Percentiles

{
    "aggs" : {
        "load_time_outlier" : {
            "percentiles" : {
                "field" : "load_time"
            }
        }
    }
}

32. Percentiles of values within specific bounds

curl -XPOST 'localhost:9200/bank/account/_search?size=0&pretty' -H 'Content-Type: application/json' -d'
{
    "aggs": {
        "balance_outlier": {
            "percentile_ranks": {
                "field": "balance",
                "values": [25000, 50000],
                "keyed": false
            }
        }
    }
}
'

33. Sum of hat prices

{
"aggs" : {
        "hat_prices" : { "sum" : { "field" : "price" } }
    }
}

34. Sort by call_duration in descending order

curl -u elastic:changeme -XGET 'localhost:9200/index-alias2-events-2015.01.01-00/_search?pretty' -H 'Content-Type: application/json' -d'
{
  "query": { "match_all": {} },
  "sort": { "call_duration": { "order": "desc" } }
}
'
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s