es查询DSL语句

布满荆棘的人生 2023-06-15 06:38 11阅读 0赞

# 1.索引index相关操作 #

# 查询所有索引
    GET _cat/indices?v
    
    # 查询某个索引下的信息
    GET /lockie_test
    
    # 新增索引
    PUT /lockie_test
    
    # 删除索引
    DELETE /lockie_test
    
    # 创建索引,同时设置mapping
    PUT lockie_test
    {
      "mappings": {
        "properties": {
          "orderNo": {
            "type": "text"
          }
        }
      }
    }

# 2.mapping相关 #

# 查询某个索引的mapping
    GET lockie_test/_mapping?pretty
    
    # 创建索引,同时设置mapping
    PUT /lockie_test/_mapping
    {
      "mappings": {
        "properties": {
          "orderNo": {
            "type": "text"
          }
        }
      }
    }
    
    
    # 添加新字段
    POST lockie_test/_mapping
    {
      "properties": {
        "dealDl": {
          "type": "double"
        },
        "balanceTime": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss"
        },
        "city": {
          "type": "text"
        }
      }
    }
    
    # 删除mapping,删除索引即可
    DELETE /lockie_test

# 3.doc相关，新增，删除，更新 #

# 新增数据 指定id使用PUT，不指定id由es随机生成使用POST
    PUT lockie_test/_doc/1
    {
      "orderNo": "201910221750150001",
      "dealDl": 2.5,
      "balanceTime": "2019-10-22 17:50:15",
      "city": "上海"
    }
    
    
    PUT lockie_test/_doc/2
    {
      "orderNo": "201910221751150002",
      "dealDl": 1.5,
      "balanceTime": "2019-10-22 17:51:15",
      "city": "北京"
    }
    
    PUT lockie_test/_doc/3
    {
      "orderNo": "201910230905150003",
      "dealDl": 20.8,
      "balanceTime": "2019-10-23 09:05:10",
      "city": "广州"
    }
    
    
    # 删除数据
    DELETE lockie_test/_doc/3
    
    # 根据查询条件删除
    POST /lockie_test/_delete_by_query?q=city:广州
    POST /lockie_test/_delete_by_query
    {
      "query":{
        "match":{
          "city": "广州"
        }
      }
    }
    
    
    # 修改数据，如果用PUT则是替换全部数据，使用POST则更新想要的数据
    PUT lockie_test/_doc/3
    {
      "orderNo": "201910230905150003"
    }
    
    # 查询更新结果发现使用PUT更新后字段只剩下orderNo
    {
      "_index" : "lockie_test",
      "_type" : "_doc",
      "_id" : "3",
      "_version" : 1,
      "_seq_no" : 32,
      "_primary_term" : 2,
      "found" : true,
      "_source" : {
        "orderNo" : "201910230905150003"
      }
    }
    
    # 如果只是想更新某个字段的值则需要使用POST，比如更新id=3的城市为深圳
    POST lockie_test/_doc/3/_update
    {
      "doc": {
        "city": "深圳",
        "remark": "这是备注3"
      }
    }
    
    # 查询更新后结果
    {
      "_index" : "lockie_test",
      "_type" : "_doc",
      "_id" : "3",
      "_version" : 5,
      "_seq_no" : 36,
      "_primary_term" : 2,
      "found" : true,
      "_source" : {
        "orderNo" : "201910230905150003",
        "dealDl" : 20.8,
        "balanceTime" : "2019-10-23 09:05:10",
        "city" : "深圳",
        "remark" : "这是备注3"
      }
    }
    
    
    # 根据条件更新，批量更新
    POST /lockie_test/_update_by_query
    {
      "script": {
        "lang": "painless",
        "inline": "ctx._source.remark='批量更新备注'"
      }
    }
    
    # 更新使用函数计算
    POST lockie_test/_doc/1/_update
    {
      "script": "ctx._source.dealDl += 1"
    }
    
    # 查看更新后的结果
    {
      "took" : 154,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 3,
          "relation" : "eq"
        },
        "max_score" : 1.0,
        "hits" : [
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "1",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910221750150001",
              "dealDl" : 3.5,
              "city" : "深圳",
              "remark" : "批量更新备注",
              "balanceTime" : "2019-10-22 17:50:15"
            }
          },
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "2",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910221751150002",
              "dealDl" : 1.5,
              "city" : "北京",
              "remark" : "批量更新备注",
              "balanceTime" : "2019-10-22 17:51:15"
            }
          },
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "3",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910230905150003",
              "dealDl" : 20.8,
              "city" : "深圳",
              "remark" : "批量更新备注",
              "balanceTime" : "2019-10-23 09:05:10"
            }
          }
        ]
      }
    }

# 4.doc的查询操作 #

# 查询所有数据
    GET lockie_test/_search
    
    {
      "took" : 619,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 3,
          "relation" : "eq"
        },
        "max_score" : 1.0,
        "hits" : [
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "2",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910221751150002",
              "dealDl" : 1.5,
              "city" : "北京",
              "remark" : "这是备注2",
              "balanceTime" : "2019-10-22 17:51:15"
            }
          },
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "3",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910230905150003",
              "dealDl" : 20.8,
              "city" : "深圳",
              "remark" : "这是备注3",
              "balanceTime" : "2019-10-23 09:05:10"
            }
          },
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "1",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910221750150001",
              "dealDl" : 3.5,
              "city" : "上海",
              "remark" : "这是备注1",
              "balanceTime" : "2019-10-22 17:50:15"
            }
          }
        ]
      }
    }
    
    # 查询所有的并且排序
    GET lockie_test/_search?sort=_id:asc
    
    # 根据id查询
    GET lockie_test/_doc/1
    
    {
      "_index" : "lockie_test",
      "_type" : "_doc",
      "_id" : "1",
      "_version" : 22,
      "_seq_no" : 43,
      "_primary_term" : 2,
      "found" : true,
      "_source" : {
        "orderNo" : "201910221750150001",
        "dealDl" : 3.5,
        "city" : "上海",
        "remark" : "这是备注1",
        "balanceTime" : "2019-10-22 17:50:15"
      }
    }
    
    # 根据其他条件查询
    GET /lockie_test/_search?q=remark:备注&sort=dealDl:desc
    
    # 查询只显示指定字段
    GET lockie_test/_doc/1?_source=orderNo,remark
    GET lockie_test/_search
    {
      "_source": ["orderNo","city","remark"]
    }
    
    {
      "_index" : "lockie_test",
      "_type" : "_doc",
      "_id" : "1",
      "_version" : 22,
      "_seq_no" : 43,
      "_primary_term" : 2,
      "found" : true,
      "_source" : {
        "orderNo" : "201910221750150001",
        "remark" : "这是备注1"
      }
    }
    
    # 分页查询from ,to分页参数
    GET lockie_test/_search
    {
      "from": 0,
      "size": 2, 
      "sort": [
        {
          "dealDl": {
            "order": "asc"
          }
        }
      ],
      "query": {
        "match_all": {}
      }
    }
    
    # match匹配查询，类似数据库的like，match_phrase将条件作为一个短语进行查询
    GET /lockie_test/_search
    {
      "query": {
        "match": {
          "city": "深圳"
        }
      }
    }
    
    # must查询，条件必须满足才能查到
    GET /lockie_test/_search
    {
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "orderNo": {
                  "value": "201910221750150001"
                }
              }
            },
            {
              "match": {
                "city": "上海"
              }
            }
          ]
        }
      }
    }
    
    {
      "took" : 1,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 1,
          "relation" : "eq"
        },
        "max_score" : 2.9424875,
        "hits" : [
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "1",
            "_score" : 2.9424875,
            "_source" : {
              "orderNo" : "201910221750150001",
              "dealDl" : 3.5,
              "city" : "上海",
              "remark" : "这是备注1",
              "balanceTime" : "2019-10-22 17:50:15"
            }
          }
        ]
      }
    }
    
    
    # should查询，类似数据库的or，条件任一个满足即可
    GET /lockie_test/_search
    {
      "query": {
        "bool": {
          "should": [
            {
              "term": {
                "orderNo": {
                  "value": "201910221750150001"
                }
              }
            },
            {
              "match": {
                "city": "深圳"
              }
            }
          ]
        }
      }
    }
    
    {
      "took" : 0,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 2,
          "relation" : "eq"
        },
        "max_score" : 1.9616584,
        "hits" : [
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "3",
            "_score" : 1.9616584,
            "_source" : {
              "orderNo" : "201910230905150003",
              "dealDl" : 20.8,
              "city" : "深圳",
              "remark" : "这是备注3",
              "balanceTime" : "2019-10-23 09:05:10"
            }
          },
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "1",
            "_score" : 0.9808292,
            "_source" : {
              "orderNo" : "201910221750150001",
              "dealDl" : 3.5,
              "city" : "上海",
              "remark" : "这是备注1",
              "balanceTime" : "2019-10-22 17:50:15"
            }
          }
        ]
      }
    }
    
    # minimum_should_match 这个是指或的条件,必须满足多少条,下面的minimum_should_match=2,所以一条都查不到
    GET /lockie_test/_search
    {
      "query": {
        "bool": {
          "should": [
            {
              "term": {
                "orderNo": {
                  "value": "201910221750150001"
                }
              }
            },
            {
              "match": {
                "city": "深圳"
              }
            }
          ],
          "minimum_should_match": 2
        }
      }
    }
    
    # must_not 排出条件值，类似数据库的not和not in
    GET /lockie_test/_search
    {
      "query": {
        "bool": {
          "must_not": [
            {
              "term": {
                "orderNo": {
                  "value": "201910230905150003"
                }
              }
            }
          ]
        }
      }
    }
    
    # range 范围查询
    GET /lockie_test/_search
    {
      "query": {
        "bool": {
          "must": [
            {
              "range": {
                "balanceTime": {
                  "gte": "2019-10-21 00:00:00",
                  "lte": "2019-10-22 23:00:00"
                }
              }
            }
          ]
        }
      }
    }
    
    
    {
      "took" : 0,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 2,
          "relation" : "eq"
        },
        "max_score" : 1.0,
        "hits" : [
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "2",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910221751150002",
              "dealDl" : 1.5,
              "city" : "北京",
              "remark" : "这是备注2",
              "balanceTime" : "2019-10-22 17:51:15"
            }
          },
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "1",
            "_score" : 1.0,
            "_source" : {
              "orderNo" : "201910221750150001",
              "dealDl" : 3.5,
              "city" : "上海",
              "remark" : "这是备注1",
              "balanceTime" : "2019-10-21 10:50:15"
            }
          }
        ]
      }
    }
    
    
    
    
    # 聚合查询，分组类似数据库的group by
    GET /lockie_test/_search
    {
      "size": 0,
      "aggs": {
        "group_by_city": {
          "terms": {
            "field": "city"
          }
        }
      }
    }
    
    
    {
      "took" : 0,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 3,
          "relation" : "eq"
        },
        "max_score" : null,
        "hits" : [
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "2",
            "_score" : null,
            "_source" : {
              "orderNo" : "201910221751150002",
              "dealDl" : 1.5,
              "city" : "北京",
              "remark" : "这是备注2",
              "balanceTime" : "2019-10-22 17:51:15"
            },
            "sort" : [
              1.5
            ]
          },
          {
            "_index" : "lockie_test",
            "_type" : "_doc",
            "_id" : "1",
            "_score" : null,
            "_source" : {
              "orderNo" : "201910221750150001",
              "dealDl" : 3.5,
              "city" : "上海",
              "remark" : "这是备注1",
              "balanceTime" : "2019-10-21 10:50:15"
            },
            "sort" : [
              3.5
            ]
          }
        ]
      }
    }
    
    
    # avg求平均值
    GET /lockie_test/_search
    {
      "size": 0, 
      "aggs": {
        "avg_dealDl": {
          "avg": {
            "field": "dealDl"
          }
        }
      }
    }
    
    {
      "took" : 3,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 3,
          "relation" : "eq"
        },
        "max_score" : null,
        "hits" : [ ]
      },
      "aggregations" : {
        "avg_dealDl" : {
          "value" : 8.6
        }
      }
    }
    
    
    # 分组聚合求平均值
    GET /lockie_test/_search
    {
      "size": 0,
      "aggs": {
        "group_by_time": {
          "terms": {
            "field": "balanceTime",
            "order": {
              "avg_dealDl": "asc"
            }
          },
          "aggs": {
            "avg_dealDl": {
              "avg": {
                "field": "dealDl"
              }
            }
          }
        }
      }
    }
    
    
    {
      "took" : 6,
      "timed_out" : false,
      "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : {
          "value" : 3,
          "relation" : "eq"
        },
        "max_score" : null,
        "hits" : [ ]
      },
      "aggregations" : {
        "group_by_time" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : 1571766675000,
              "key_as_string" : "2019-10-22 17:51:15",
              "doc_count" : 1,
              "avg_dealDl" : {
                "value" : 1.5
              }
            },
            {
              "key" : 1571655015000,
              "key_as_string" : "2019-10-21 10:50:15",
              "doc_count" : 1,
              "avg_dealDl" : {
                "value" : 3.5
              }
            },
            {
              "key" : 1571821510000,
              "key_as_string" : "2019-10-23 09:05:10",
              "doc_count" : 1,
              "avg_dealDl" : {
                "value" : 20.8
              }
            }
          ]
        }
      }
    }