elasticsearch 查询示例
1. 代码含义: 当前索引ext_cms_article，查询条件伪代码是：article_type=2 AND (article_tabnames OR article_title OR article_content OR article_author) 其中operator:and 查询命中率高， 具体含义看官方介绍
post ext_cms_article/_search?error_trace=true&pretty=true&typed_keys=true { "from": 0, "query": { "bool": { "must": [ { "term": { "article_type": { "value": 2 } } }, { "bool": { "should": [ { "match": { "article_tabnames": { "operator": "and", "query": "李永进" } } }, { "match": { "article_title": { "operator": "and", "query": "李永进" } } }, { "match": { "article_short": { "operator": "and", "query": "李永进" } } }, { "match": { "article_content": { "operator": "and", "query": "李永进" } } }, { "match": { "article_author": { "operator": "and", "query": "李永进" } } } ] } } ] } }, "size": 10 }
后面测试发现：搜索词:宝宝冬g 搜索不出来，因为加了字母g, 将operator:or 能查询出来
1.1 另一种写法，minimum_should_match是指 should条件中至少匹配一个条件
post vendor_quote_platform/_search { "query": { "bool": { "must": [ { "term": { "VendorFlag": 13 } }, { "range": { "CreateDate": { "gte": "2022-09-07T02:25:38.870670" } } } ], "should": [ { "term": { "Keyword": "bu406" } }, { "term": { "PN": "BU406" } } ], "minimum_should_match": 1 } }, "sort": [ { "CreateDate": { "order": "desc" } } ], "from": 0, "size": 30 }
2. Count查询带条件
GET collect_material_page_address/_count { "query": { "term": { "IsCollectUri": { "value": "true" } } } }
3. 条件and查询
POST /collect_material_page_address/_search { "query": { "bool": { "must": [ { "term": { "IsCollectUri": { "value": false } } }, { "term": { "VendorName": { "value": "future" } } } ] } } }
var res = pageAddressEsModel.Search(s =>s.Index(Indices.Index("collect_material_page_address")).Size(num) //.Sort(s=>s.Ascending(d=>d.CreateTime)) .Sort(s => s.Descending(d => d.CreateTime)) .Query(q => q.Bool ( b=>b.Must(m=> m.Term(t => t.IsCollectUri, false) && m.Term(t=>t.VendorName,vendorName)) ))); return res.Documents.ToList();
4 条件查询 where IsCollectUri=0 and VendorName='future' and CollectUri not like('https://www.futureelectronics.cn/search?q=TT%20Electronics%20-%20IRC%')
get collect_material_page_address/_search?error_trace=true&pretty=true&typed_keys=true { "query": { "bool": { "must": [ { "bool": { "must": [ { "term": { "IsCollectUri": { "value": false } } }, { "term": { "VendorName": { "value": "future" } } } ], "must_not": [ { "wildcard": { "CollectUri": { "value": "https://www.futureelectronics.cn/search?q=TT%20Electronics%20-%20IRC*" } } } ] } } ] } }, "size": 10, "sort": [ { "CreateTime": { "order": "desc" } } ] }
var res = pageAddressEsModel.Search(s => s.Index(Indices.Index("collect_material_page_address")).Size(num) //.Sort(s=>s.Ascending(d=>d.CreateTime)) .Sort(s => s.Descending(d => d.CreateTime)) .Query(q => q.Bool ( b => b.Must(m => m.Term(t => t.IsCollectUri, false) && m.Term(t => t.VendorName, vendorName) && m.Bool(b => b.MustNot(m => m.Wildcard(w => w.CollectUri, "https://www.futureelectronics.cn/search?q=TT%20Electronics%20-%20IRC*")) )))));
5. 查询需要的字段 select PN,Brand from vendor_quote_platform where VendorFlag=29 and DataSource=2 and CreateTime>='2022-01-21 00:00:01'
post vendor_quote_platform/_search { "track_total_hits": true, "from": 0, "query": { "bool": { "must": [ { "term": { "VendorFlag": { "value": 29 } } }, { "term": { "DataSource": { "value": 2 } } }, { "range": { "CreateTime": { "gte": "2022-01-21 00:00:01" } } } ] } }, "size": 25, "sort": [ { "CreateTime": { "order": "desc" } }, { "PN": { "order": "asc" } } ], "_source": { "includes": [ "PN", "Brand", "StockQty", "DataSource", "CreateTime", "Currency", "IsValidCrawl", "PriceItems" ] } }
var querys = new List<Func<QueryContainerDescriptor<VendorQuotePlatformEsModel>, QueryContainer>>(); querys.Add(q => q.Term(r => r.VendorFlag, (int)vendorFlag)); querys.Add(q => q.Term(r => r.IsValidCrawl, true)); //必须是有效抓取 //来源于官网 querys.Add(q => q.Term(r => r.DataSource, 2)); if (!string.IsNullOrWhiteSpace(condition.Condition.PN) && condition.Condition.IsExact) querys.Add(q => q.Term(r => r.PN, condition.Condition.PN)); if (!string.IsNullOrWhiteSpace(condition.Condition.PN) && !condition.Condition.IsExact) querys.Add(q => q.Wildcard(r => r.PN, "*"+condition.Condition.PN+"*")); if(!string.IsNullOrWhiteSpace(condition.Condition.QueryKeywords)) querys.Add(q => q.Wildcard(r => r.PN, "*" + condition.Condition.QueryKeywords + "*")); //获取最新一天的数据 if (condition.Condition.BeginTime == null && condition.Condition.EndTime == null) { var todaystr = DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:01"; // DateTime today =DateTime.Parse(todaystr); querys.Add(q => q.DateRange(d => d.Field(r => r.CreateTime).GreaterThanOrEquals(todaystr))); } if (condition.Condition.BeginTime != null) { var beginTimestr = condition.Condition.BeginTime.Value.ToString("yyyy-MM-dd HH:mm:ss"); querys.Add(q => q.DateRange(d => d.Field(r => r.CreateTime).GreaterThanOrEquals(beginTimestr))); } if (condition.Condition.EndTime != null) { var endTimestr = condition.Condition.EndTime.Value.ToString("yyyy-MM-dd HH:mm:ss"); querys.Add(q => q.DateRange(d => d.Field(r => r.CreateTime).LessThanOrEquals(endTimestr))); } if (condition.Condition.IsExact) querys.Add(q => q.Term(r => r.IsExact, true)); //排序 Func<SortDescriptor<VendorQuotePlatformEsModel>, IPromise<IList<ISort>>> sort = s => { s.Descending(o => o.CreateTime); s.Ascending(o => o.PN); return s; }; var searchRes = _vendorQuotePlatformClient.Search(r => r.Bool(b => b.Must(querys)), sort, condition.PageIndex, condition.PageSize, r => r.PN, r => r.Brand, r => r.StockQty, r => r.DataSource, r => r.CreateTime, r => r.Currency, r => r.IsValidCrawl, r => r.PriceItems);
6. 查询索引中文档MaterialSpecs字段必须存在的数据 (索引结构中有MaterialSpecs字段，但新增文档时如果没有MaterialSpecs字段，那么该文档就不存在MaterialSpecs字段）
GET collect_material_info/_search { "query": { "exists": { "field": "MaterialSpecs" } } }
7.查询索引中子文档字段必须存在的数据（SPQItems[] 也不在统计中）
post crawl_material_spq/_count
{
"query" :{
"nested": {
"path": "SPQItems",
"query": {
"exists": {
"field": "SPQItems.Brand"
}
}
}
}
}
8.查询字段为空的数据，对应C#的实现
get sku_goods_resource_down/_search { "query": { "bool": { "must": [ { "term": { "Status": { "value": 1 } } }, { "term": { "ResourceUriSSO": { "value": "" } } } ] } } }
var result = _esGoodsResource.Search(s => s.Bool(b => b.Must(m => m.Term(t => t.Status, 1) && m.Term(c => c.Verbatim().Field(p => p.ResourceUriSSO).Value("")))), s => s.Ascending(a => a.CreateTimeStamp), 1, top);
9.查询ResourceItems子对象中ResourceType字段数据包含1和2的值
get sku_goods/_count { "query" :{ "nested": { "path": "ResourceItems", "query": { "terms": { "ResourceItems.ResourceType": [1,2] } } } } }
10. 查询多个品牌是否存在于多个字段中：Brand,BrandCnName,BrandEnName，子表AliasItems.AliasName 中的任意一个字段
get sku_brand/_search { "track_total_hits": true, "from": 0, "query": { "bool": { "minimum_should_match": 1, "should": [ { "terms": { "Brand": [ "INFINEON TECHNOLOGIES AG", "EUPEC GMBH & CO KG", "TEXAS INSTRUMENTS" ] } }, { "terms": { "BrandCnName": [ "INFINEON TECHNOLOGIES AG", "EUPEC GMBH & CO KG", "TEXAS INSTRUMENTS" ] } }, { "terms": { "BrandEnName": [ "INFINEON TECHNOLOGIES AG", "EUPEC GMBH & CO KG", "TEXAS INSTRUMENTS" ] } }, { "nested": { "path": "AliasItems", "query": { "terms": { "AliasItems.AliasName": [ "INFINEON TECHNOLOGIES AG", "EUPEC GMBH & CO KG", "TEXAS INSTRUMENTS" ] } } } } ] } }, "size": 10000, "sort": [ { "CreateTimeStamp": { "order": "asc" } } ], "_source": { "includes": [] } }
