ES查询语句汇集[高版本]

增删改查

# 新增索引
PUT /索引名称
{
  "settings": {
    "refresh_interval": "-1"
  },
  "mappings": {
    "properties": {
      "location": {
        "type": "geo_point"
      },
      "date": {
        "type": "date",
        "format": "yyyy-MM-dd"
      }
    }
  }
}
# 新增索引ID是系统生成
POST /索引名称/_doc/
{
  "a": 1,
  "b": 2
}
# 新增索引ID是1，修改也是通过这种方式
POST /索引名称/_doc/1
{
  "a": 1,
  "b": 2
}
# 删除索引
DELETE /索引名称/_doc/1

聚合分组查询

GET /索引名称/_search
{
  "size": 0,
  "aggs": {
    "groupDate": {
      "date_histogram": {
        "field": "create_date",
        "interval": "day",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

# 时间分割不同 _search 分为 Query阶段，Fetch阶段
GET /索引名称/_search
{
  "size": 0,
  "aggs": {
    "groupDate": {
      "date_histogram": {
        "field": "create_date",
        "interval": "90m",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

统计某个字段出现的次数

统计某字段出现的次数
{
  "aggs": {
    "field": {
      "terms": {
        "field": "wenshuNum",
        "size": 50
      }
    }
  }
}



SELECT DISTINCT(user_id) FROM table WHERE user_id_type = 3;


{
  "query": {
    "term": {
      "user_id_type": 3
    }
  },
  "collapse": {
    "field": "user_id"
  }
}



SELECT COUNT(DISTINCT(user_id)) FROM table WHERE user_id_type = 3;
{
  "query": {
    "term": {
      "user_id_type": 3
    }
  },
  "aggs": {
    "count": {
      "cardinality": {
        "field": "user_id"
      }
    }
  }


SELECT COUNT(user_id) FROM table GROUP BY user_id_type;
{
  "aggs": {
    "user_type": {
      "terms": {
        "field": "user_id_type"
      }
    }
  }
}


SELECT COUNT(DISTINCT(user_id)) FROM table GROUP BY user_id_type;

{
  "aggs": {
    "user_type": {
      "terms": {
        "field": "user_id_type"
      },
      "aggs": {
        "count": {
          "cardinality": {
            "field": "user_id"
          }
        }
      }
    }
  }
}



根据某个地理地址统计
GET sports/_search
{
  "size": 0,
  "aggregations": {
    "baseball_player_ring": {
      "geo_distance": {
        "field": "location",
        "unit": "km", 
        "origin": {
          "lat": 46.12,
          "lon": -68.55
        },
        "ranges": [
          {
            "from": 0,
            "to": 300
          }
        ]
      }
    }
  }
}




根据某个字段统计取前多少条
GET sports/_search
{
  "size": 0,
  "aggs": {
    "sport": {
      "terms": {
        "field": "sport.keyword",
        "size": 3
      }
    }
  }
}



根据某个字段进行统计
GET sports/_search
{
  "size": 0,
  "aggs": {
    "sport_count": {
      "value_count": {
        "field": "sport.keyword"
      }
    }
  }
}

查询结果

优化部分