Es常用Elasticsearch SQL查询语句

Es常用Elasticsearch SQL查询语句

# 创建索引
PUT /patent/
{
  "mappings": {
    "properties": {
      "title": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
      },
      "citationCount": {
        "type": "long"
      },
      "assigneeHarmonizedCount": {
        "type": "long"
      },
      "inventorHarmonizedCount": {
        "type": "long"
      }
    }
  }
}

# 自动创建索引，并新增一条记录
POST user/student/1
{
  "name": "小明",
  "age":  20,
  "birdth":  "2001-09-10"
}

# sql查询方法
POST /_sql?format=txt
{
  "query": "SELECT countryCode FROM patent",
  "fetch_size": 2
}

# match查询
GET /patent/_doc/_search
{
  "query": {
    "match":{
      "countryCode":"CN"
    }
  }
}

# term查询
GET /patent/_search
{
  "query": {
    "term": {
      "citationCount": 2
    }
  }
}

# 自动索引的字段，需要字段后加.keyword
GET /patent/_doc/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "countryCode.keyword": "AR"
          }
        }
      ]
    }
  }
}

# 多条件查询
GET /event*/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "dataFrom": "其他"
          }
        }
      ],
      "must_not": [
        {
          "term": {
            "eventFrom": "情报"
          }
        },
        {
          "term": {
            "eventFrom": "otherDiscover"
          }
        }
      ]
    }
  }
}

# 多条件查询
GET /event*/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "dataFrom": "其他"
          }
        },
        {
          "term": {
            "eventFrom": "otherDiscover"
          }
        }
      ]
    }
  }
}

# 条件删除
POST /event*/_doc/_delete_by_query?wait_for_completion=false
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "dataFrom": "其他"
          }
        },
        {
          "term": {
            "eventFrom": "otherDiscover"
          }
        }
      ]
    }
  }
}

# 空值字段值，条件删除数据
POST /patent/_delete_by_query?scroll_size=5000&wait_for_completion=false
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "publicationNumber"
        }
      }
    }
  }
}

# 更新字段值
POST /index_name/type/id/_update
{
  "doc": {
    "column": "column_value"
  }
}

# 多条件时间范围查询
GET /event*/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "dataFrom": "其他"
          }
        },
        {
          "range": {
            "endTime": {
              "gte": "2021-9-01 00:00:00",
              "lte": "2021-10-01 00:00:00"
            }
          }
        }
      ]
    }
  }
}

# 聚合分组查询（doc_count聚合）
POST /patent/_search
{
  "size": 0,
  "aggs": {
    "field": {
      "terms": {
        "field": "publicationNumber.keyword",
        "size": 10000,
        "min_doc_count": 1
      }
    }
  }
}


# 索引创建时指定分页最大限制
PUT /patent/_settings?preserve_existing=true 
{
  "max_result_window": "20000000000"
} 

# 给这个索引追加一个新的字段，同时给这个字段指定类型
PUT /patent/_mapping
{
  "properties": {
    "citationCount":{
      "type": "text",
      "fields": {
        "citationCount":{
          "type":"keyword",
          "ignore_above" : 256
        }
      }
    },
    "timestamp" : {
        "type" : "date"
    }
  }
}


# 重建索引，数据迁移
POST /_reindex
{
  "source": {
    "index": "old_index"
  },
  "dest": {
    "index": "new_index"
  }
}