基础ES语句整理

 

由于项目中会运用到ES搜索引擎，整理了一下测试会常用到一些查询语句。每条语句都和pgsql有对应，比较好理解。

1、 Select * from table

      GET /[index]/_search

 

2、Select * from table where filed='123'

GET /[index]/_search

{

  "query": {

    "bool": {

      "must": [

        {

        "term": {

          "filed": {

            "value": "123"

          }

        }

        }

      ]

    }

  }

}

 

3、Select * from table where filed !='123'

GET /[index]/_search

{

  "query": {

    "bool": {

      "must_not": [

        {

        "term": {

          "filed": {

            "value": "123"

          }

        }

        }

      ]

    }

  }

}

 

4、Select * from table where filed  in ('123','234')

GET /[index]/_search

{
  "query": {
    "terms": {
      "FIELD": [
        "VALUE1",
        "VALUE2"
      ]
    }
  }
}

5、Select * from table where filed  like '%123%'

GET /[index]/_search

{

 "query": {

   "wildcard": {

     "ins_name": {

       "value": "*123*"

     }

   }

 }

}

 

6、Select * from table where filed like '123%' and filed2 like '20'

GET /[index]/_search

{

 "query": {

  "bool": {

    "must": [

      {"wildcard": {

        "filed ": {

          "value": "123*"

        }

      }},{"wildcard": {

        "filed2 ": {

          "value": "20"

        }

      }}

    ]

  }

}}

 

7、Select * from table where filed like '123%'  or filed2 like '20'

GET /[index]/_search

{

 "query": {

  "bool": {

    "should": [

      {"wildcard": {

        "filed ": {

          "value": "123*"

        }

      }},{"wildcard": {

        "filed2 ": {

          "value": "20"

        }

      }}

    ]

  }

}}

 

 

 

8、Select * from table where order by desc[acs]

GET /[index]/_search

{

 "sort": [

   {

     "created_date": {

       "order": "desc[asc]"

     }

   }

 ]

}

 

 

9、Select * from table limit 10

GET /[index]/_search

{

 "size": 20

}

 

10、Select * from table filed>=5 and filed<=7

GET /[index]/_search

{

 "query": {

  "bool": {

    "should": [

      {

        "range": {

          "filed": {

            "gte":5,

            "lte": 7

          }

        }

      }

    ]

  }

}}

 

 

11、Select * from table between  filed='2020-10-10' and filed2=''2021-10-10'';

GET /[index]/_search

{

 "query": {

  "bool": {

    "should": [

      {

        "range": {

          "filed": {

            "gte":"2020-10-10",

            "lte": "2021-10-10"

          }

        }

      }

    ]

  }

}}

 

12、Update  table set filed='test' where id='1579'

更新需要用到内容的主键id去更新。查询时可以不用到type，但是插入或更新操作需要加上type

POST /[index]/[type]/[_id]/_update

{

  "doc": {

    "filed":"test"

  }

}

 

13、Insert into table  filed values('test');

POST /[index]/[type]

{

  "filed":"test"

}