Elastic Search Queries

elastic search query

GET players/_search
{
  "track_total_hits": true
}

GET players/_search
{
  "query": {
    "range": {
      "@timestamp": {
        "gte": "1994-02-01",
        "lte": "2000-02-01"
      }
    }
  }
}

GET players/_search
{
  "aggs": {
    "by_gender": {
      "terms": {
        "field": "gender",
        "size": 10
      }
    }
  }
}

GET players/_search 
{
  "query": {
    "match": {
      "gender": "femal"
    }
  },
  "aggs": {
    "firstNameWithA": {
      "significant_text": {
        "field": "lastname"
      }
    }
  }
}

GET players/_search
{
  "query": {
    "match": {
      "competition": {
        "query": "LaLiga SmartBank",
        "minimum_should_match": "3"
      }
    }
  }
}

GET /_alias?pretty=true

GET jobs/_search

GET jobs/_search
{
"query": {
  "match": {
    "Industry": {
      "query": "Advertising"
    }
  }
}
}

GET jobs/_search
{
"query": {
  "multi_match": {
    "query": "Purchase Executive",
    "fields": [
      "Role^2",
      "Job Title"
      ],
      "type": "phrase"
  }
}
}

GET jobs/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match_phrase": {
            "Industry": "Electricals, Switchgears"
          }
        }
      ],
      "must_not": [
        {
          "match": {
            "Location": "Bengaluru"
          }
        }
      ],
      "should": [
        {
          "match": {
            "Location": "Gurgaon,Manesar"
          }
        }
      ],
      "filter": [
        {
          "range": {
            "sal": {
              "gte": 6061
            }
          }
        }
      ]
    }
  }
}

GET ecommerce/_search
{
  "track_total_hits": true,
  "size": 20
}


GET ecommerce/_search
{
  "aggs": {
    "sum_of_quantity_sold": {
      "sum": {
        "field": "Quantity"
      }
    }
  }
}

GET ecommerce/_search
{
  "size": 0, 
  "aggs": {
    "sum_of_quantity_sold": {
      "sum": {
        "field": "Quantity"
      }
    }
  }
}
GET ecommerce/_search
{
  "size": 0,
  "aggs": {
    "Lowest Unit Price": {
      "min": {
        "field": "UnitPrice"
      }
    }
  }
}
GET ecommerce/_search
{
  "size": 0,
  "aggs": {
    "Max Unit Price": {
      "max": {
        "field": "UnitPrice"
      }
    }
  }
}
GET ecommerce/_search
{
  "size": 0,
  "aggs": {
    "Ag Unit Price": {
      "avg": {
        "field": "UnitPrice"
      }
    }
  }
}

GET ecommerce/_search
{
  "size": 0,
  "aggs": {
    "Unit Price Stats": {
      "stats": {
        "field": "UnitPrice"
      }
    }
  }
  
  
}

// Cardinality aggs computes the unique values for a given field
GET ecommerce/_search
{
  "size": 0,
  "aggs": {
    "No of Unique Customers": {
      "cardinality": {
        "field": "CustomerID"
      }
    }
  }
}
// limit aggs to only a country
GET ecommerce/_search
{
  "query": {
    "match": {
      "Country": "Germany"
    }
  },
  "aggs": {
    "No of Unique Customers": {
      "cardinality": {
        "field": "CustomerID"
      }
    }
  }
}
GET ecommerce_data/_search
{
  "size": 0,
  "aggs": {
    "transactions_by_8_hours": {
      "date_histogram": {
        "field": "InvoiceDate",
        "fixed_interval": "8h"
      }
    }
  }
}

GET ecommerce_data/_search
{
  "size": 0,
  "aggs": {
    "transactions_by_8_hours": {
      "date_histogram": {
        "field": "InvoiceDate",
        "calendar_interval": "1M",
        "order": {
          "_key": "desc"
        }
      }
    }
  }
}

GET ecommerce_data/_search
{
  "size": 0,
  "aggs": {
    "transactions_per_price_interval": {
      "histogram": {
        "field": "UnitPrice",
        "interval": "10",
        "order": {
          "_key": "desc"
        }
      }
    }
  }
}

// cannot sort it
GET ecommerce_data/_search
{
   "size": 0,
  "aggs": {
    "transactions_per_custom_price_range": {
      "range": {
        "field": "UnitPrice",
        "ranges": [
          {
            "to": 100
          },
          {
            "from": 100,
            "to": 200
          },
          {
            "from": 200
          }
        ]
      }
    }
  }
}

GET ecommerce_data/_search
{
  "size": 0,
  "aggs": {
    "top_5_customers": {
      "terms": {
        "field": "CustomerID",
        "size": 5
      }
    }
  }
}

GET ecommerce_data/_search
{
  "size": 0,
  "aggs": {
    "top_5_customers_with_lowest_transactions": {
      "terms": {
        "field": "CustomerID",
        "size": 5,
        "order": {
          "_count": "asc"
        }
      }
    }
  }
}
// mappings done before
PUT ecommerce_data
{
  "mappings": {
    "properties": {
      "Country": {
        "type": "keyword"
      },
      "CustomerID": {
        "type": "long"
      },
      "Description": {
        "type": "text"
      },
      "InvoiceDate": {
        "type": "date",
        "format": "M/d/yyyy H:m"
      },
      "InvoiceNo": {
        "type": "keyword"
      },
      "Quantity": {
        "type": "long"
      },
      "StockCode": {
        "type": "keyword"
      },
      "UnitPrice": {
        "type": "double"
      }
    }
  }
}

POST _reindex
{
  "source": {
    "index": "ecommerce"
  },
  "dest": {
    "index": "ecommerce_data"
  }
}

POST ecommerce_data/_delete_by_query
{
  "query": {
    "range": {
      "UnitPrice": {
        "lte": 0
      }
    }
  }
}

POST ecommerce_data/_delete_by_query
{
  "query": {
    "range": {
      "UnitPrice": {
        "gte": 500
      }
    }
  }
}
GET ecommerce_data/_mapping

POST temp_index/_doc
{
  "name": "Pineapple",
  "botanical_name": "Ananas comosus",
  "produce_type": "Fruit",
  "country_of_origin": "New Zealand",
  "date_purchased": "2020-06-02T12:15:35",
  "quantity": 200,
  "unit_price": 3.11,
  "description": "a large juicy tropical fruit consisting of aromatic edible yellow flesh surrounded by a tough segmented skin and topped with a tuft of stiff leaves.These pineapples are sourced from New Zealand.",
  "vendor_details": {
    "vendor": "Tropical Fruit Growers of New Zealand",
    "main_contact": "Hugh Rose",
    "vendor_location": "Whangarei, New Zealand",
    "preferred_vendor": true
  }
}

GET temp_index/_search
{
  "_source": [
    "vendorDetial"]
}


GET temp_index/_mapping

PUT temp_index/_mapping
{
  "properties": {
    "vendorDetial": {
      "type": "keyword"
    }
  }
}

POST temp_index/_update_by_query?conflicts=proceed&refresh=true
{
"query" : {
"match_all" : {}
},
"script" : "ctx._source.vendorDetial = ctx._source.vendor_details.vendor_location;"
}