kibana的Dev Tool中如何對es進行增刪改查

2020 年 6 月 9 日
筆記
elasticseach的增刪改查, elasticsearch, kibana dev tool對es的增刪改查

kinaba Dev Tool中對es(elasticSearch)進行增刪改查

一、查詢操作

查詢語句基本語法

以下語句類似於mysql的： select * from xxx.yyy.topic where 條件1，條件2，…條件N

GET xxx.yyy.topic/logs/_search

{

 "query": {
    "bool": {
        這裡面是限制條件，不寫則查所有數據
        可以包含單個或多個限制條件
    }
  }
}

select * from xxx.yyy.topic where 條件1

GET xxx.yyy.topic/logs/_search
{
  "query": {
     這裡只能是單個條件
    }
}

詳細說明：

GET xxx.yyy.topic/logs/_search中

xxx.yyy.topic 對應欄位_index 即索引欄位相當於mysql中的資料庫名稱

logs 對應欄位_type 相當於mysql中的表名

_id 相當於mysql中的主鍵

_search 這表示執行查詢操作

_source 相當於mysql表中的列的集合

bool體中是一個或多個基本查詢的組合，可在bool裡面使用以下參數：

must 文檔中必須包含must後的條件

must_not 文檔中必須不包含must_not後的條件

should 滿足should後的任何一個條件

filter filter後跟過濾條件

1、select * from xxx.yyy.topic

GET xxx.yyy.topic/logs/_search
{
  "query": {
     "bool": {
           "must": {
              "match_all": {}
           }
      }

  }
}

或者

GET xxx.yyy.topic/logs/_search
{
  "query": {
     "bool": {}
  }
}

或者

GET xxx.yyy.topic/logs/_search
{
  "query": {
     "match_all": {}
  }
}

如果在index下只有一種_type，則在GET中可以不帶_type

GET xxx.yyy.topic/_search
{
  "query": {
     "bool": {}
  }
}

2、 select * from xxx.yyy.topic where login = ‘BigFaceCat’ and pwd=’123′

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "login.keyword": "BigFaceCat"
          }
        },
        {
          "match": {
            "pwd.keyword": "123"
          }
        }
      ]
    }
  }
}

3、查詢語句 select * from xxx.yyy.topic where update_time > 1591200000000 and update_time<1591200000000

select * from xxx.yyy.topic where update_time between 1591200000000 and 1591200000000

GET xxx.yyy.topic/logs/_search

{
    "query":{
        "bool":{
            "must":[
                {
                    "range":{
                        "update_time":{
                            "gte":1591200000000,
                            "lte":1591362000000
                        }
                    }
                }
            ]
        }
    }
}

GET xxx.yyy.topic/logs/_search
{
    "query":{
        "bool":{
            "filter":[
                {
                    "range":{
                        "update_time":{
                            "gte":1591200000000,
                            "lte":1591362000000
                        }
                    }
                }
            ]
        }
    }
}

gte :表示 >=

lte : 表示<=

gt : 表示>

lt : 表示<

4、查詢語句 select * from xxx.yyy.topic where update_time > 1591200000000 and update_time<1591200000000 and login=’BigFaceCat’

GET xxx.yyy.topic/logs/_search
{
    “query”:{
        “bool”:{
            “must“:[
                {
                    “range”:{
                        “update_time”:{
                            “gte”:1591200000000,
                            “lte”:1591362000000
                        }
                    }
                },
                {
                    “match”:{
                        “login.keyword”:“BigFaceCat”
                    }
                }
            ]
        }
    }
}

GET  xxx.yyy.topic/logs/_search
{
   "query": {
    "bool": {
      "must":{ "match":{"login.keyword":"BigFaceCat"}},
      "filter": {
          "range": {
               "update_time": {
                 "gte": 1591200000000,
                 "lte": 1591362000000
                       }
                   }
         }
        
      }
    }
}

5、查詢語句 select * from xxx.yyy.topic where login=’BigFaceCat’ or login=’LittlteFaceCat’

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      "should": [
        { "match": { "login.keyword":  "BigFaceCat" }},
        { "match": { "login.keyword":  "LittlteFaceCat"}}
      ]
    }
  }
}

6、select * from xxx.yyy.topic where login is null

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "login"
        }
      }
    }
  }
}

7、select * from xxx.yyy.topic where login is not mull

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      "must": {
        "exists": {
          "field": "login"
        }
      }
    }
  }
}

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      "filter": {
        "exists": {
          "field": "login"
        }
      }
    }
  }
}

8、select * from xxx.yyy.topic where login in (‘BigFaceCat’,’LittlteFaceCat’)

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": { "login.keyword": "BigFaceCat" }
        },
        {
          "match": { "login.keyword": "LittlteFaceCat" }
          
        }
      ]
    }
  }
}

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool":{
       "filter":{
        "terms":{ "login":["BigFaceCat","LittleFaceCat"]}
      }
    }
  }
}

GET xxx.yyy.topic/logs/_search
{
“query”: {
“bool”:{
“should“: [
{ “term“:{ “login”:”BigFaceCat”} },
{ “term“:{ “login”:”LittleFaceCat”} }
]
}
}
}

terms : 後面可跟多個值

term : 後面只能有一個值

9、select call_id , record_id from xxx.yyy.topic where _id = ‘eecd25747’

GET xxx.yyy.topic/logs/_search
{
  "_source": ["call_id","record_id"], 
  "query": {
    "match": { "_id": "eecd25747a"}
  }
  
}

select call_id , record_id from xxx.yyy.topic where login=’BigFaceCat’ and pwd=’123′

GET xxx.yyy.topic/logs/_search
{
  "_source": ["call_id","record_id"], 
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "login.keyword": "BigFaceCat"
          }
        },
        {
          "match": {
            "pwd.keyword": "123"
          }
        }
      ]
    }
  }
}

10、聚合查詢 select sum( talk_duration) as sum_of_talkDuration from xxx.yyy.topic

GET  xxx.yyy.topic/logs/_search
{
  "aggs": {
    "sum_of_talkDuration":{
      "sum":{
        "field": "talk_duration"
      }
    }
    
  }
}

select sum( talk_duration) as sum_of_talkDuration from xxx.yyy.topic where end_time is not null

GET  xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      "must": [
        {"exists":{"field":"end_time"}}
      ]
    }
  }, 
  
  "aggs": {
    "sum_of_talkDuration":{
      "sum":{
        "field": "talk_duration"
      }
    }
    
  }
}

11、select SUM( DISTINCT talk_duration) as sum_of_diffTalkDuration from xxx.yyy.topic

GET  xxx.yyy.topic/logs/_search
{
  "size":0,
  "aggs": {
    "sum_of_diffTalkDuration":{
      "cardinality":{
        "field": "talk_duration"
      }
    }
    
  }
}

12、求平均值 SELECT AVG( record_duration ) as avg_of_talkDurtion FROM xxx.yyy.topic

GET  xxx.yyy.topic/logs/_search
{
  "size":0,
  "aggs": {
    "avg_of_talkDuration":{
       "avg":{
        "field": "talk_duration"
      }
    }
  }

13、求最大值 SELECT MAX( record_duration ) as max_of_talkDurtion FROM xxx.yyy.topic

GET  xxx.yyy.topic/logs/_search
{
  "size":0,
  "aggs": {
    "max_of_talkDuration":{
       "max":{
        "field": "talk_duration"
      }
    }
  }

14、對查詢結果排序 select * from xxx.yyy.topic order by talk_duration desc

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": { }
  }, 
 
  "sort": [{ "talk_duration": "desc" }]
    
  }

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {}
   },  
  "sort": [
      { "talk_duration": {"order": "desc"} }
  ]
  
}

desc : 降序排序

asc : 升序排序

15、分頁查詢

GET xxx.yyy.topic/logs/_search
{
  "query": {
    "bool": {
      
        }
  },  
  "sort": [{ "talk_duration": {"order": "desc"} }],
   "from": 2,
   "size": 3
  
}

from : 起始頁

size : 按size條記錄分頁

如上查詢：按每頁3條記錄分頁，返回第2頁

16、通過主鍵_id查詢 select * from xxx.yyy.topic where _id = ‘AXKRp4hXdhuuEZQaKj7n’

GET xxx.yyy.topic/logs/AXKRp4hXdhuuEZQaKj7n

17、通過主鍵_id查詢某些欄位 select phone_number,system_code, extension from xxx.yyy.topic where _id=’AXKRp4hXdhuuEZQaKj7′

GET xxx.yyy.topic/logs/AXKRp4hXdhuuEZQaKj7n?_source=phone_number,system_code,extension

二、修改操作

1、通過查詢條件來限定修改範圍的方式

update xxx.yyy.topic set result=‘[{\”aWord\”:\”1哈哈哈哈\”,\”count\”:1,\”locations\”:[\”00:05-00:08\”,\”01:01-01:02\”]}]’

where update_time >= 1591200000000 and update_time <= 1591362000000

json串中帶有特殊字元”，需要用\進行轉義

POST  xxx.yyy.topic/logs/_update_by_query
{
   "query": {
    "bool": {
      "must": [
         {
          "range": {
              "update_time": {
                 "gte": 1591200000000,
                 "lte": 1591362000000
                       }
                   }
         }
        ]
      }
    },
    "script": {
         "source": "ctx._source['result']='[{\"aWord\":\"1哈哈哈哈\",\"count\":1,\"locations\":[\"00:05-00:08\",\"01:01-01:02\"]}]'"
    }
}

2、用主鍵作作為條件修改的方式

update xxx.yyy.topic set result='[{\”aWord\”:\”1哈哈哈哈\”,\”count\”:1,\”locations\”:[\”00:05-00:08\”,\”01:01-01:02\”]}’

where _id=’xx-b2fc-43ca-afe7-77e3ff406ff9‘

注意：json中帶有特殊字元,需要兩個”””包起來

POST xxx.yyy.topic/logs/xx-b2fc-43ca-afe7-77e3ff406ff9/_update
{
“doc”:{
　　　　”result”: “””[{“aWord”:”1哈哈哈哈”,”count”:1,”locations”:[“00:05-00:08″,”01:01-01:02”]}]“””
　　}
}

POST xxx.yyy.topic/logs/xx-b2fc-43ca-afe7-77e3ff406ff9/_update

{ “doc”:{

    "result": "哈哈哈" }
}

Tags: elasticseach的增刪改查 elasticsearch kibana dev tool對es的增刪改查

kibana的Dev Tool中如何對es進行增刪改查

kinaba Dev Tool中對es(elasticSearch)進行增刪改查

一、查詢操作

二、修改操作

VirMach 便宜 VPS

QNews

kibana的Dev Tool中如何對es進行增刪改查

kinaba Dev Tool中對es(elasticSearch)進行增刪改查

一、查詢操作

二、修改操作

分享此文：

Related Posts

嚴重指出，「從頭到尾」的讀書方法，是害人的

最新版Swagger 3升級指南和新功能體驗！

解開CPU基頻限制：華擎Z490 ITX電競小鋼炮主板2499元開賣

為什麼男人有喉結 女人卻沒有？答案出乎意料…

VirMach 便宜 VPS

QNews

熱門搜尋

為什麼男人有喉結女人卻沒有？答案出乎意料…