Curl localhost:9200 curl localhost:9200

Mysql > select * from ‘mysql’

The field types are ingeger,keyword

{
    "term": { "field":111}}Copy the code

The field type is text

{
    "match": { "field":"value"}}Copy the code

“Like” in mysql multi-keyword fuzzy query

{"multi_match": {"query": "search for a word, or a keyword ", "type": "best_fields", "fields": ["field1^3", "field2"], "tie_breaker": 0.3}},Copy the code

Normally the best_fields type uses the score of the single best matching field, but if tie_breaker is specified, then it calculates the score as follows:

the score from the best matching field plus tie_breaker *_score for all other matching fields

Normally, the best_fields type uses the score of a single best-matched field, but if tie_breaker is specified, it calculates the score as follows:

Score for best matching field + tie_breaker *_score (for all other matching fields)

Es uses THE IK Chinese word segmentation plug-in, can achieve word segmentation query, synonyms query and other functions.

Mysql > select * from ‘in’

    {
        "terms": {
          "username": ["Zhang"."Bill"]}}Copy the code

“Distance” in mysql

"Geo_distance" : {" short ":" 20 km, "" distance_type" : "plane", "location" : {" lat ":" 41.024124 ", "says lon" : "12.212352"}}Copy the code

To query data within 20km, the type of the location field is geo_point

Mysql doesn’t have any ready-made functions, so you have to implement them in meters

    select 6378.138 * 2 * ASIN(
                    SQRT(
                        POW(
                            SIN( ( :lat * PI(a) /180 - shop_lat * PI(a) /180 ) / 2  ),
                            2
                        ) + COS(:lat * PI(a) /180) * COS(shop_lat * PI(a) /180) * POW(
                            SIN( (  :lng * PI(a) /180 - shop_lng * PI(a) /180 ) / 2  ),  2))) *1000
               AS distance
Copy the code

Aggregation Aggregation

Es’s aggregation is powerful and enables many complex queries through nested aggregation

    {
        "aggs":{
            "aggName":{
                ...
                "aggs":{
                    "subAggName1":{
                        ...
                    },
                    "subAggName2":{
                        ...
                    }
                }
            }
        }
    }
Copy the code

“Group by” Group query

Terms aggregation method

    {
        "group1":
              "terms": {
                  "field": "group1"."size": 10."order": {
                    "score_title": "desc"."score_price": "asc"}}}Copy the code

This method does not support paging. To paging, use composite aggregation

{ "group1": "composite":{ "sources" : [ { "group1": { "terms" : { "field": "group1" } } } ], "size": 1, # = 0 limit "after": 0 # = 0 offset "aggs":{"items": {"top_hits": { (" * "), # show all fields "size" : "5", "sort" : {# for each items in the order "_score" : {" order ":" desc} ", "price" : {" order ": "asc" } } } } } } }Copy the code

“Count | sum | Max” in mysql query statistics

Various data can be counted by adding sub-aggregations to aggregation, such as the total price

    "score_price": {
        "sum": {
          "field": "price"
        }
    }
Copy the code

Statistical keywords Score

      "score_title": {
        "max": {
          "script": {
            "source": "_score"
          }
        }
      }
Copy the code

Statistical distance (toDO gets distance value)

      "distance": {
        "geo_distance": {
          "field": "location",
          "origin": "todo",
          "distance_type" : "plane",
          "ranges": [
            {
              "to": 100000
            }
          ]
        },
        "aggs": {
          "distance_value": {
            "max": {
              "script": {
                "source": "_score"
              }
            }
          }
        }
      }
Copy the code

“Order by” in mysql

This is achieved by subaggregation +bucket_sort

"agggs":{ ... "My_sort" : {" bucket_sort ": {" sort" : {" score_title ":" desc ", # defined above aggration "score_price" : "asc"}}}.}Copy the code