es查询命令备份(只需要网页9200/_plugin/head/就可以访问)
本文只是写一些常用es命令,这里不用任何客户端,只用 9200/_plugin/head/ 那个端口网页就可以,然后是复合查询。
注意es其实一个idnex只能有一个type,如果一个index做了多个type,坑。。。太多了,首先 where不能用,然后 聚合也不能用。至少在es2.*是这样的,这个坑你可以搜索下“_type Field Not Available for Aggregation #5634”
1. 查询所有
关键字:match_all
等同SQL
select * from item.ic_spu; { "query": { "match_all": {} } }
2. 根据某个字段查询 – 默认匹配
关键字:match
等同SQL
select * from item.ic_spu where id = 20001137 ; { "query": { "match": { "id": "20001137" } } } { "query": { "match": { "tags.tagId": "30000" } } }
根据文本查询(有分词)
{
"query": {
"match": {
"title": "黄连丸"
}
}
}
这三种查询都会在item_repository/item库中匹配出的结果示例
{ "_index": "item_repository", "_type": "item", "_id": "20001137", "_version": 101, "found": true, "_source": { "seller": { "name": "好药师官方旗舰店", "alias": "好药师大药房", "id": 989810607 }, "modifiedTime": "2015-12-31 11:31:04.000", "img": "T1E3YlByJT1RCvBVdK|T14ddTBsZT1RCvBVdK|T1jdCTBjxT1RCvBVdK|T1fJETBjWT1RCvBVdK|T1IJdTBjAT1RCvBVdK", "skus": [{ "hcodePrice": 0, "origPrice": 1500, "outerId": "2115", "id": 20001513, "price": 1528, "status": 2 }], "title": "清胃黄连丸", "type": 20, "tags": [{ "code": "webFronts2", "tagName": "五官科疾病", "tagId": 30017, "domain": "mall_sales" }, { "code": "o2oFronts", "tagName": "感冒发烧", "tagId": 40002, "domain": "mall_sales" }, { "code": "webFronts1", "tagName": "中西药品", "tagId": 30000, "domain": "mall_sales" }, { "code": "webFronts3", "tagName": "口腔/咽喉", "tagId": 30132, "domain": "mall_sales" }, { "code": "webFronts", "tagName": "前台根节点", "tagId": 2, "domain": "mall_sales" }, { "code": "normal", "tagName": "normal", "tagId": 20839, "domain": "mall_sales" }], "timeStamp": "2016-12-27 16:00:30.758", "subTitle": "", "createTime": "2015-12-31 11:31:04.000", "indexId": "20001137", "subType": 15, "id": 20001137, "status": 2 } }
3.根据某个字段查询 – 绝对匹配
关键字:match + phrase
JQuery示例
根据id查询
{ "query": { "match": { "id": { "query": "20001137", "type": "phrase" } } } }
注意:类似 id 等数值类型的属性,默认匹配和绝对匹配结果是相同的
对于文本类的属性,没有做分词(not_analyzed)的属性,绝对匹配时用属性完整的值可以匹配出预期结果,例如item库中的tags.domain
如果设置了analyzer,用属性完整的值去做绝对匹配可能会无结果,如item库中的title属性
原因:这种情况下的匹配,是用属性完整的值去匹配索引中被分词后的属性值,自然就可能匹配不上。
例如某spu名称为“感冒颗粒”,那么索引中实际上存储的是分词后的“感冒”和“颗粒”,此时用“感冒颗粒”去匹配,当然无法绝对匹配到“感冒”或者“颗粒”
根据文本查询(针对无analyzer的属性)
等同SQL
select * from item.ic_tag where domain = "mall_sales" ; { "query": { "match": { "tags.domain": { "query": "mall_sales", "type": "phrase" } } } }
这些查询都会在item_repository/item库中匹配出的结果示例
{ "_index": "item_repository", "_type": "item", "_id": "20001137", "_version": 101, "found": true, "_source": { "seller": { "name": "好药师官方旗舰店", "alias": "好药师大药房", "id": 989810607 }, "modifiedTime": "2015-12-31 11:31:04.000", "img": "T1E3YlByJT1RCvBVdK|T14ddTBsZT1RCvBVdK|T1jdCTBjxT1RCvBVdK|T1fJETBjWT1RCvBVdK|T1IJdTBjAT1RCvBVdK", "skus": [{ "hcodePrice": 0, "origPrice": 1500, "outerId": "2115", "id": 20001513, "price": 1528, "status": 2 }], "title": "清胃黄连丸", "type": 20, "tags": [{ "code": "webFronts2", "tagName": "五官科疾病", "tagId": 30017, "domain": "mall_sales" }, { "code": "o2oFronts", "tagName": "感冒发烧", "tagId": 40002, "domain": "mall_sales" }, { "code": "webFronts1", "tagName": "中西药品", "tagId": 30000, "domain": "mall_sales" }, { "code": "webFronts3", "tagName": "口腔/咽喉", "tagId": 30132, "domain": "mall_sales" }, { "code": "webFronts", "tagName": "前台根节点", "tagId": 2, "domain": "mall_sales" }, { "code": "normal", "tagName": "normal", "tagId": 20839, "domain": "mall_sales" }], "timeStamp": "2016-12-27 16:00:30.758", "subTitle": "", "createTime": "2015-12-31 11:31:04.000", "indexId": "20001137", "subType": 15, "id": 20001137, "status": 2 } }
4. 根据某个字段查询 – 通配符匹配
关键字:wildcard
?代表任意1个字符,*代表任意多个字符
JQuery示例
注意:类似 id 等数值类型的属性,也可使用通配符匹配,如同sql中的like查询
对于文本类的属性,纯粹的通配符匹配(即:预期返回以查询keyword做like查询的结果)只适用于没有做分词(not_analyzed)的属性,例如item库中的tags.domain
如果设置了analyzer,通配符匹配也要结合分词去考虑,实际上只能用分词加通配符匹配出预期结果,例如“ *白药* ”可以匹配出“云南白药膏”,但是“ *白药牙* ”就匹配不出。
查看索引分词结果可用 GET /item_repository/_analyze?analyzer=shennong-max&pretty=true&text=(云南白药膏)
根据文本查询(针对无analyzer的属性)
等同SQL
select * from item.ic_tag where domain like "?al%" ; { "query": { "wildcard": { "tags.domain": "?al*" } } }
这些查询都会在item_repository/item库中匹配出的结果示例
{
"_index": "item_repository",
"_type": "item",
"_id": "20001137",
"_version": 101,
"found": true,
"_source": {
"seller": {
"name": "好药师官方旗舰店",
"alias": "好药师大药房",
"id": 989810607
},
"modifiedTime": "2015-12-31 11:31:04.000",
"img": "T1E3YlByJT1RCvBVdK|T14ddTBsZT1RCvBVdK|T1jdCTBjxT1RCvBVdK|T1fJETBjWT1RCvBVdK|T1IJdTBjAT1RCvBVdK",
"skus": [{
"hcodePrice": 0,
"origPrice": 1500,
"outerId": "2115",
"id": 20001513,
"price": 1528,
"status": 2
}],
"title": "清胃黄连丸",
"type": 20,
"tags": [{
"code": "webFronts2",
"tagName": "五官科疾病",
"tagId": 30017,
"domain": "mall_sales"
},
{
"code": "o2oFronts",
"tagName": "感冒发烧",
"tagId": 40002,
"domain": "mall_sales"
},
{
"code": "webFronts1",
"tagName": "中西药品",
"tagId": 30000,
"domain": "mall_sales"
},
{
"code": "webFronts3",
"tagName": "口腔/咽喉",
"tagId": 30132,
"domain": "mall_sales"
},
{
"code": "webFronts",
"tagName": "前台根节点",
"tagId": 2,
"domain": "mall_sales"
},
{
"code": "normal",
"tagName": "normal",
"tagId": 20839,
"domain": "mall_sales"
}],
"timeStamp": "2016-12-27 16:00:30.758",
"subTitle": "",
"createTime": "2015-12-31 11:31:04.000",
"indexId": "20001137",
"subType": 15,
"id": 20001137,
"status": 2
}
}
5.根据某个字段查询 – 前缀匹配
关键字:prefix
JQuery示例
注意:可以把前缀匹配理解为一种特殊的通配符匹配
对于文本类的属性,前缀匹配和通配符匹配一样需要考虑analyzed的影响
根据文本查询(针对无analyzer的属性)
等同SQL
select * from item.ic_tag where domain like "mal%" ; { "query": { "prefix": { "tags.domain": "mal" } } }
6. 根据某个字段查询 – 范围匹配
关键字:range
JQuery示例
等同SQL
select * from item.ic_spu where id >= 20001137 and id <= 20001140;
注意:范围匹配对数值类型和时间类型的属性都适用
{
"query": {
"range": {
"id": {
"include_lower": true,
"from": "20001137",
"include_upper": true,
"to": "20001140"
}
}
}
}
{
"query": {
"range": {
"modifiedTime": {
"include_lower": false,
"from": "2015-12-31 11:31:04.000",
"include_upper": true,
"to": "2015-12-31 11:31:15.000"
}
}
}
}
7. AND 查询
关键字:bool + must
JQuery示例
{ "query": { "bool": { "must": [ { "match": { "type": 20 } }, { "match": { "subType": 15 } }, { "query": { "wildcard": { "title": "*感冒*" } } } ] } } }
这些查询会在item_repository/item库中匹配出的结果示例
{
"_index": "item_repository",
"_type": "item",
"_id": "20001122",
"_version": 102,
"found": true,
"_source": {
"seller": {
"name": "好药师官方旗舰店",
"alias": "好药师大药房",
"id": 989810607
},
"modifiedTime": "2015-12-31 11:31:03.000",
"img": "T1.BYlB4LT1RCvBVdK|T1rdDTBjWT1RCvBVdK|T1FdKTBsxT1RCvBVdK|T17dKTBjDT1RCvBVdK|T1vOATBjLT1RCvBVdK",
"skus": [{
"hcodePrice": 0,
"origPrice": 1100,
"outerId": "2099",
"id": 20008366,
"price": 1078,
"status": 2
}],
"title": "感冒软胶囊",
"type": 20,
"tags": [{
"code": "o2oFronts",
"tagName": "感冒发烧",
"tagId": 40002,
"domain": "mall_sales"
},
{
"code": "webFronts",
"tagName": "前台根节点",
"tagId": 2,
"domain": "mall_sales"
},
{
"code": "webFronts1",
"tagName": "中西药品",
"tagId": 30000,
"domain": "mall_sales"
},
{
"code": "normal",
"tagName": "normal",
"tagId": 20839,
"domain": "mall_sales"
},
{
"code": "webFronts3",
"tagName": "感冒发烧",
"tagId": 30190,
"domain": "mall_sales"
},
{
"code": "webFronts2",
"tagName": "感冒咳嗽",
"tagId": 30037,
"domain": "mall_sales"
}],
"timeStamp": "2016-12-27 18:00:31.438",
"subTitle": "",
"createTime": "2015-12-31 11:31:03.000",
"indexId": "20001122",
"subType": 15,
"id": 20001122,
"status": 2
}
}
8. OR 查询
关键字:bool + should + minimum_should_match
JQuery示例
注意:"minimum_should_match": 1 可以不写,表示should结构体中至少得满足一个条件,即通常意义上的or,这也是默认值。
当然,对于should结构体中包括>2个条件的情况,可以设m来构造必须满足其中m个条件的广义OR查询
例如,下面的JQuery,如果设成2 其实也等同于AND查询
{ "query": { "bool": { "should": [ { "match": { "type": 20 } }, { "match": { "tags.domain": { "query": "mall_admin", "type": "phrase" } } } ], "minimum_should_match": 1 } }
这些查询都会在item_repository/item库中匹配出的结果示例
{
"_index": "item_repository",
"_type": "item",
"_id": "20011265",
"_version": 73,
"found": true,
"_source": {
"modifiedTime": "2016-01-20 17:22:53.000",
"img": "T1m5xXByVT1RXrhCrK",
"skus": [{
"hcodePrice": 0,
"origPrice": 3000,
"id": 911911888303103,
"price": 3000,
"status": 2
}],
"title": "名医商品2436560600",
"type": 35,
"tags": [{
"code": "treeBackground2",
"tagName": "名医咨询",
"tagId": 13,
"domain": "mall_admin"
},
{
"code": "treeBackground",
"tagName": "后台节点根",
"tagId": 880154,
"domain": "mall_admin"
},
{
"code": "treeBackground1",
"tagName": "旧商品类目",
"tagId": 1,
"domain": "mall_admin"
},
{
"code": "treeBackground3",
"tagName": "名医",
"tagId": 14,
"domain": "mall_admin"
}],
"timeStamp": "2016-12-27 16:00:49.954",
"subTitle": "名医咨询",
"createTime": "2016-01-20 17:22:53.000",
"indexId": "20011265",
"subType": 0,
"id": 20011265,
"status": 2
}
}
9. NOT 查询
关键字:bool + must_not
JQuery示例
根据id查询
等同SQL
select * from item.ic_spu where type != 20 ; { "query": { "bool": { "must_not": [ { "match": { "type": 20 } } ] } } }
根据文本查询
等同SQL
select * from item.ic_tag where domain <> 'mall_sales' ; { "query": { "bool": { "must_not": [ { "match": { "tags.domain": { "query": "mall_sales", "type": "phrase" } } } ] } } }
这些查询会在item_repository/item库中匹配出的结果示例
{
"_index": "item_repository",
"_type": "item",
"_id": "20011265",
"_version": 73,
"found": true,
"_source": {
"modifiedTime": "2016-01-20 17:22:53.000",
"img": "T1m5xXByVT1RXrhCrK",
"skus": [{
"hcodePrice": 0,
"origPrice": 3000,
"id": 911911888303103,
"price": 3000,
"status": 2
}],
"title": "名医商品2436560600",
"type": 35,
"tags": [{
"code": "treeBackground2",
"tagName": "名医咨询",
"tagId": 13,
"domain": "mall_admin"
},
{
"code": "treeBackground",
"tagName": "后台节点根",
"tagId": 880154,
"domain": "mall_admin"
},
{
"code": "treeBackground1",
"tagName": "旧商品类目",
"tagId": 1,
"domain": "mall_admin"
},
{
"code": "treeBackground3",
"tagName": "名医",
"tagId": 14,
"domain": "mall_admin"
}],
"timeStamp": "2016-12-27 16:00:49.954",
"subTitle": "名医咨询",
"createTime": "2016-01-20 17:22:53.000",
"indexId": "20011265",
"subType": 0,
"id": 20011265,
"status": 2
}
}
10. 分页与排序
关键字:from + size + sort
JQuery示例
根据id查询
等同SQL
select * from item.ic_spu order by gmt_modified limit 1,5; { "size": "5", "from": "1", "sort": [ { "modifiedTime": "asc" } ] }
11.展示属性限制
关键字:fields
JQuery示例
等同SQL
select id,title from item.ic_spu ; { "fields": [ "id", "title"] }
最后,用一个案例做下总结:
如果想要查 type=20(药), subType !=10(非处方药), status=2(上架状态), title中包含"感冒"(商品名中包含“感冒”)或者 tags.tagName中包含"感冒"(标签名中包含“感冒”)的所有spu,以modifiedTime(spu修改时间)一维倒排+ id 二维顺排(modifiedTime相同的情况下用id从小到大排序),取前10个,返回结果中只需要spu的id,title,modifiedTime,seller.name(售卖商户名),那么对应的JQuery如下:
{ "size": "10", "from": "0", "sort": [ { "modifiedTime": "desc" }, { "id": "asc" } ], "query": { "bool": { "must": [ { "match": { "type": 20 } }, { "bool": { "must_not": [ { "match": { "subType": 10 } } ] } }, { "match": { "status": 2 } }, { "bool": { "should": [ { "match": { "tags.tagName": "感冒" } }, { "match": { "title": "感冒" } } ], "minimum_should_match": 1 } } ] } }, "fields": [ "id", "title", "seller.name", "modifiedTime" ] }
此案例也可以这么写
因为should查询中匹配的keyword是相同的,只是field不同,所以可以改成
{ "size": "10", "from": "0", "sort": [ { "modifiedTime": "desc" }, { "id": "asc" } ], "query": { "bool": { "must": [ { "match": { "type": 20 } }, { "bool": { "must_not": [ { "match": { "subType": 10 } } ] } }, { "match": { "status": 2 } }, { "multi_match": { "query": "感冒", "fields": [ "title", "tags.tagName" ] } } ] } }, "fields": [ "id", "title", "seller.name", "modifiedTime" ] }