nested query (嵌套查询)
文档中可能包含嵌套类型的字段,这些字段用来索引一些数组对象,每个对象都可以作为一条独立的文档被查询出来(用嵌套查询)
场景:商店(shop) 活动(act) 目标是将活动(act)嵌套(冗余)到shop数据下面
1、index_mapping
PUT _template/shop
{
"order": 0,
"template": "shop-*",
"settings": {
"index": {
"mapping": {
"total_fields": {
"limit": "50000"
}
},
"refresh_interval": "1s",
"number_of_shards": "10",
"number_of_replicas": "1"
}
},
"mappings": {
"shop": {
"properties": {
"end_date": {
"format": "yyyy-MM-dd",
"type": "date"
},
"update_time": {
"format": "yyyy-MM-dd HH:mm:ss.SSSSSS",
"type": "date"
},
"coordinate": {
"type": "geo_point"
},
"create_time": {
"format": "yyyy-MM-dd HH:mm:ss.SSSSSS",
"type": "date"
},
"acts": {
"type": "nested",
"properties": {
"end_date": {
"format": "yyyy-MM-dd",
"type": "date"
},
"update_time": {
"format": "yyyy-MM-dd HH:mm:ss.SSSSSS",
"type": "date"
},
"create_time": {
"format": "yyyy-MM-dd HH:mm:ss.SSSSSS",
"type": "date"
},
"start_date": {
"format": "yyyy-MM-dd",
"type": "date"
}
}
},
"start_date": {
"format": "yyyy-MM-dd",
"type": "date"
}
}
}
},
"aliases": {}
}
2、商店与活动嵌套数据
PUT shop-2017.12.18/shop/351971
{
"status": 1,
"city": {
"id": 587,
"name": "黄石"
},
"update_time": "2018-01-09 14:41:12.892000",
"tel": [
"0714-6289788"
],
"name": "江小鱼江鱼馆(黄石店)",
"tags": [
"餐饮服务",
"餐饮相关场所",
"餐饮相关"
],
"tags_enrich": {
"name": "美食",
"id": 10
},
"brand": {
"id": 35572,
"name": "江小鱼江鱼馆"
},
"acts": [
{
"zone_exclude_id": [],
"create_time": "2017-11-30 03:57:18.732000",
"id": 269791,
"subject": {
"type": 1,
"name": "bank"
},
"category": [
{
"name_en": "",
"id": 316,
"name": "中餐"
}
],
"special_dates": [],
"original_url": "https://piao.o2o.cmbchina.com/shopwindow/weixinProductDetail?cityNo=714&productNo=9917112800061",
"special_weekdays": [],
"content": """
##### 参与方式
- 在招行「掌上生活 APP」购劵
- 至「江小鱼江鱼馆」使用
##### 活动内容
- 活动期间,招商银行信用卡持卡人**每周三上午 10:00** 至招行「掌上生活 APP」 50 元购「江小鱼江鱼馆」价值 100 元代金券
##### 注意事项
- 每单仅限使用 5 张
- 酒水饮料、主食除外
- 仅限周三使用
- 不与商户其他优惠同享
- 具体使用细则请以银行和门店公告为准
""",
"usage_scene": [
{
"name_en": "food",
"id": 2,
"name": "餐饮"
}
],
"start_date": "2017-11-29",
"status": 1,
"subject_description": "招商银行信用卡持卡人",
"update_time": "2018-01-02 15:50:46.481000",
"card_orgs": [],
"end_date": "2018-03-31",
"bank_logo_enrich": {
"logo_url": "https://cdn.xhqb.com/bank/picture_xh/11.png"
},
"receive_description": "",
"unavailable_time_ranges": [],
"quota": "数量有限,先到先得",
"zone_include": [
"黄石"
],
"discount": [
{
"name_en": "free_coupon",
"id": 22,
"name": "享券"
}
],
"category_enrich": {
"name": "美食",
"id": 10
},
"act_area": null,
"content_enrich": {
"参与方式": [
"在招行「掌上生活 APP」购劵",
"至「江小鱼江鱼馆」使用"
],
"活动内容": [
"活动期间,招商银行信用卡持卡人每周三上午 10:00 至招行「掌上生活 APP」 50 元购「江小鱼江鱼馆」价值 100 元代金券"
],
"注意事项": [
"每单仅限使用 5 张",
"酒水饮料、主食除外",
"仅限周三使用",
"不与商户其他优惠同享",
"具体使用细则请以银行和门店公告为准"
]
},
"payment": [
{
"name_en": "",
"id": 169,
"name": "网银快捷支付"
}
],
"small_img_url": null,
"act_type": 1,
"zone_include_id": [
587
],
"banks_enrich": {
"name": "招商",
"id": 11
},
"original_title": "(周三5折)江小鱼江鱼馆100元代金券",
"display_tags": [],
"big_img_url": "https://qnpic.billbear.cn/FriUhDNz2c4KICWvyc-ETd-xk7Gt",
"available_time_ranges": [
{
"range_type": 2,
"start_time": 36000,
"end_time": 86100,
"weekday": 2,
"day": null
}
],
"zone_exclude": [],
"banks": [
{
"id": 11,
"name": "招商银行"
}
],
"title": "江小鱼江鱼馆 100 元代金券 (周三 5 折)",
"is_hot": 0,
"money_limit": null
},
{
"zone_exclude_id": [],
"create_time": "2017-11-29 13:16:43.594000",
"id": 269793,
"subject": {
"type": 1,
"name": "bank"
},
"category": [
{
"name_en": "",
"id": 316,
"name": "中餐"
}
],
"special_dates": [],
"original_url": "https://piao.o2o.cmbchina.com/shopwindow/weixinProductDetail?cityNo=714&productNo=9917112800078",
"special_weekdays": [],
"content": """
##### 参与方式
- 在招行「掌上生活 APP」购劵
- 至「江小鱼江鱼馆」使用
##### 活动内容
- 活动期间,招商银行信用卡持卡人至招行「掌上生活 APP」 80 元购「江小鱼江鱼馆」价值 100 元代金券
##### 注意事项
- 不与商户其他优惠同享
- 酒水饮料、主食除外
- 具体使用细则请以银行和门店公告为准
""",
"usage_scene": [
{
"name_en": "food",
"id": 2,
"name": "餐饮"
}
],
"start_date": "2017-11-29",
"status": 1,
"subject_description": "招商银行信用卡持卡人",
"update_time": "2018-01-02 15:54:00.766000",
"card_orgs": [],
"end_date": "2018-03-31",
"bank_logo_enrich": {
"logo_url": "https://cdn.xhqb.com/bank/picture_xh/11.png"
},
"receive_description": "",
"unavailable_time_ranges": [],
"quota": "数量有限,先到先得",
"zone_include": [
"黄石"
],
"discount": [
{
"name_en": "free_coupon",
"id": 22,
"name": "享券"
}
],
"category_enrich": {
"name": "美食",
"id": 10
},
"act_area": null,
"content_enrich": {
"参与方式": [
"在招行「掌上生活 APP」购劵",
"至「江小鱼江鱼馆」使用"
],
"活动内容": [
"活动期间,招商银行信用卡持卡人至招行「掌上生活 APP」 80 元购「江小鱼江鱼馆」价值 100 元代金券"
],
"注意事项": [
"不与商户其他优惠同享",
"酒水饮料、主食除外",
"具体使用细则请以银行和门店公告为准"
]
},
"payment": [
{
"name_en": "",
"id": 169,
"name": "网银快捷支付"
}
],
"small_img_url": null,
"act_type": 1,
"zone_include_id": [
587
],
"banks_enrich": {
"name": "招商",
"id": 11
},
"original_title": "江小鱼江鱼馆100元代金券",
"display_tags": [],
"big_img_url": "https://qnpic.billbear.cn/FsuJGkUVpxOwCfkrenCZLsrFpxX_",
"available_time_ranges": [
{
"range_type": 1,
"start_time": 0,
"end_time": 86400,
"weekday": null,
"day": null
}
],
"zone_exclude": [],
"banks": [
{
"id": 11,
"name": "招商银行"
}
],
"title": "江小鱼江鱼馆 100 元代金券",
"is_hot": 0,
"money_limit": null
}
],
"label": "have_act",
"create_time": "2018-01-09 14:40:48.846000",
"city_enrich": {
"region": "华中地区",
"name": "黄石",
"level": 4
},
"address": "黄石港区天津路52号十五中对面",
"coordinate": {
"lat": 30.216619,
"lon": 115.08882
},
"id": 351971
}
3、商店与活动的双向查询 查询shop
GET shop-2018.01.10/shop/_search
{
"query": {
"bool": {
"must": [
{
"term": {
"name.keyword": {
"value": "江小鱼江鱼馆(黄石店)"
}
}
}
]
}
}
}
返回数据
{
"took": 11,
"timed_out": false,
"_shards": {
"total": 10,
"successful": 10,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 8.536735,
"hits": [
{
"_index": "shop-2018.01.10",
"_type": "shop",
"_id": "351971",
"_score": 8.536735,
"_source": {
"status": 1,
"city": {
"id": 587,
"name": "黄石"
},
"update_time": "2018-01-09 14:41:12.892000",
"tel": [
"0714-6289788"
],
"name": "江小鱼江鱼馆(黄石店)",
"tags": [
"餐饮服务",
"餐饮相关场所",
"餐饮相关"
],
"tags_enrich": {
"name": "美食",
"id": 10
},
"brand": {
"id": 35572,
"name": "江小鱼江鱼馆"
},
"acts": [
{
"zone_exclude_id": [],
"create_time": "2017-11-30 03:57:18.732000",
"id": 269791,
"subject": {
"type": 1,
"name": "bank"
},
"category": [
{
"name_en": "",
"id": 316,
"name": "中餐"
}
],
"special_dates": [],
"original_url": "https://piao.o2o.cmbchina.com/shopwindow/weixinProductDetail?cityNo=714&productNo=9917112800061",
"special_weekdays": [],
"content": """
##### 参与方式
- 在招行「掌上生活 APP」购劵
- 至「江小鱼江鱼馆」使用
##### 活动内容
- 活动期间,招商银行信用卡持卡人**每周三上午 10:00** 至招行「掌上生活 APP」 50 元购「江小鱼江鱼馆」价值 100 元代金券
##### 注意事项
- 每单仅限使用 5 张
- 酒水饮料、主食除外
- 仅限周三使用
- 不与商户其他优惠同享
- 具体使用细则请以银行和门店公告为准
""",
"usage_scene": [
{
"name_en": "food",
"id": 2,
"name": "餐饮"
}
],
"start_date": "2017-11-29",
"status": 1,
"subject_description": "招商银行信用卡持卡人",
"update_time": "2018-01-02 15:50:46.481000",
"card_orgs": [],
"end_date": "2018-03-31",
"bank_logo_enrich": {
"logo_url": "https://cdn.xhqb.com/bank/picture_xh/11.png"
},
"receive_description": "",
"unavailable_time_ranges": [],
"quota": "数量有限,先到先得",
"zone_include": [
"黄石"
],
"discount": [
{
"name_en": "free_coupon",
"id": 22,
"name": "享券"
}
],
"category_enrich": {
"name": "美食",
"id": 10
},
"act_area": null,
"content_enrich": {
"参与方式": [
"在招行「掌上生活 APP」购劵",
"至「江小鱼江鱼馆」使用"
],
"活动内容": [
"活动期间,招商银行信用卡持卡人每周三上午 10:00 至招行「掌上生活 APP」 50 元购「江小鱼江鱼馆」价值 100 元代金券"
],
"注意事项": [
"每单仅限使用 5 张",
"酒水饮料、主食除外",
"仅限周三使用",
"不与商户其他优惠同享",
"具体使用细则请以银行和门店公告为准"
]
},
"payment": [
{
"name_en": "",
"id": 169,
"name": "网银快捷支付"
}
],
"small_img_url": null,
"act_type": 1,
"zone_include_id": [
587
],
"banks_enrich": {
"name": "招商",
"id": 11
},
"original_title": "(周三5折)江小鱼江鱼馆100元代金券",
"display_tags": [],
"big_img_url": "https://qnpic.billbear.cn/FriUhDNz2c4KICWvyc-ETd-xk7Gt",
"available_time_ranges": [
{
"range_type": 2,
"start_time": 36000,
"end_time": 86100,
"weekday": 2,
"day": null
}
],
"zone_exclude": [],
"banks": [
{
"id": 11,
"name": "招商银行"
}
],
"title": "江小鱼江鱼馆 100 元代金券 (周三 5 折)",
"is_hot": 0,
"money_limit": null
},
{
"zone_exclude_id": [],
"create_time": "2017-11-29 13:16:43.594000",
"id": 269793,
"subject": {
"type": 1,
"name": "bank"
},
"category": [
{
"name_en": "",
"id": 316,
"name": "中餐"
}
],
"special_dates": [],
"original_url": "https://piao.o2o.cmbchina.com/shopwindow/weixinProductDetail?cityNo=714&productNo=9917112800078",
"special_weekdays": [],
"content": """
##### 参与方式
- 在招行「掌上生活 APP」购劵
- 至「江小鱼江鱼馆」使用
##### 活动内容
- 活动期间,招商银行信用卡持卡人至招行「掌上生活 APP」 80 元购「江小鱼江鱼馆」价值 100 元代金券
##### 注意事项
- 不与商户其他优惠同享
- 酒水饮料、主食除外
- 具体使用细则请以银行和门店公告为准
""",
"usage_scene": [
{
"name_en": "food",
"id": 2,
"name": "餐饮"
}
],
"start_date": "2017-11-29",
"status": 1,
"subject_description": "招商银行信用卡持卡人",
"update_time": "2018-01-02 15:54:00.766000",
"card_orgs": [],
"end_date": "2018-03-31",
"bank_logo_enrich": {
"logo_url": "https://cdn.xhqb.com/bank/picture_xh/11.png"
},
"receive_description": "",
"unavailable_time_ranges": [],
"quota": "数量有限,先到先得",
"zone_include": [
"黄石"
],
"discount": [
{
"name_en": "free_coupon",
"id": 22,
"name": "享券"
}
],
"category_enrich": {
"name": "美食",
"id": 10
},
"act_area": null,
"content_enrich": {
"参与方式": [
"在招行「掌上生活 APP」购劵",
"至「江小鱼江鱼馆」使用"
],
"活动内容": [
"活动期间,招商银行信用卡持卡人至招行「掌上生活 APP」 80 元购「江小鱼江鱼馆」价值 100 元代金券"
],
"注意事项": [
"不与商户其他优惠同享",
"酒水饮料、主食除外",
"具体使用细则请以银行和门店公告为准"
]
},
"payment": [
{
"name_en": "",
"id": 169,
"name": "网银快捷支付"
}
],
"small_img_url": null,
"act_type": 1,
"zone_include_id": [
587
],
"banks_enrich": {
"name": "招商",
"id": 11
},
"original_title": "江小鱼江鱼馆100元代金券",
"display_tags": [],
"big_img_url": "https://qnpic.billbear.cn/FsuJGkUVpxOwCfkrenCZLsrFpxX_",
"available_time_ranges": [
{
"range_type": 1,
"start_time": 0,
"end_time": 86400,
"weekday": null,
"day": null
}
],
"zone_exclude": [],
"banks": [
{
"id": 11,
"name": "招商银行"
}
],
"title": "江小鱼江鱼馆 100 元代金券",
"is_hot": 0,
"money_limit": null
}
],
"label": "have_act",
"brand_logo_enrich": {
"logo_url_lx": "https://cdn.xhqb.com/brand/picture_lx/35572.png",
"logo_url_xh": "https://cdn.xhqb.com/brand/picture_xh/35572.jpg"
},
"create_time": "2018-01-09 14:40:48.846000",
"city_enrich": {
"region": "华中地区",
"name": "黄石",
"level": 4
},
"address": "黄石港区天津路52号十五中对面",
"coordinate": {
"lat": 30.216619,
"lon": 115.08882
},
"id": 351971
}
}
]
}
}
4、查询act
GET shop-2018.01.10/shop/_search
{
"query": {
"nested": {
"path": "acts",
"query": {
"bool": {
"must": [
{
"term": {
"acts.title.keyword": {
"value": "江小鱼江鱼馆 100 元代金券"
}
}
}
]
}
}
}
}
}
返回数据
{
"took": 18,
"timed_out": false,
"_shards": {
"total": 10,
"successful": 10,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 8.879055,
"hits": [
{
"_index": "shop-2018.01.10",
"_type": "shop",
"_id": "351971",
"_score": 8.879055,
"_source": {
"status": 1,
"city": {
"id": 587,
"name": "黄石"
},
"update_time": "2018-01-09 14:41:12.892000",
"tel": [
"0714-6289788"
],
"name": "江小鱼江鱼馆(黄石店)",
"tags": [
"餐饮服务",
"餐饮相关场所",
"餐饮相关"
],
"tags_enrich": {
"name": "美食",
"id": 10
},
"brand": {
"id": 35572,
"name": "江小鱼江鱼馆"
},
"acts": [
{
"zone_exclude_id": [],
"create_time": "2017-11-30 03:57:18.732000",
"id": 269791,
"subject": {
"type": 1,
"name": "bank"
},
"category": [
{
"name_en": "",
"id": 316,
"name": "中餐"
}
],
"special_dates": [],
"original_url": "https://piao.o2o.cmbchina.com/shopwindow/weixinProductDetail?cityNo=714&productNo=9917112800061",
"special_weekdays": [],
"content": """
##### 参与方式
- 在招行「掌上生活 APP」购劵
- 至「江小鱼江鱼馆」使用
##### 活动内容
- 活动期间,招商银行信用卡持卡人**每周三上午 10:00** 至招行「掌上生活 APP」 50 元购「江小鱼江鱼馆」价值 100 元代金券
##### 注意事项
- 每单仅限使用 5 张
- 酒水饮料、主食除外
- 仅限周三使用
- 不与商户其他优惠同享
- 具体使用细则请以银行和门店公告为准
""",
"usage_scene": [
{
"name_en": "food",
"id": 2,
"name": "餐饮"
}
],
"start_date": "2017-11-29",
"status": 1,
"subject_description": "招商银行信用卡持卡人",
"update_time": "2018-01-02 15:50:46.481000",
"card_orgs": [],
"end_date": "2018-03-31",
"bank_logo_enrich": {
"logo_url": "https://cdn.xhqb.com/bank/picture_xh/11.png"
},
"receive_description": "",
"unavailable_time_ranges": [],
"quota": "数量有限,先到先得",
"zone_include": [
"黄石"
],
"discount": [
{
"name_en": "free_coupon",
"id": 22,
"name": "享券"
}
],
"category_enrich": {
"name": "美食",
"id": 10
},
"act_area": null,
"content_enrich": {
"参与方式": [
"在招行「掌上生活 APP」购劵",
"至「江小鱼江鱼馆」使用"
],
"活动内容": [
"活动期间,招商银行信用卡持卡人每周三上午 10:00 至招行「掌上生活 APP」 50 元购「江小鱼江鱼馆」价值 100 元代金券"
],
"注意事项": [
"每单仅限使用 5 张",
"酒水饮料、主食除外",
"仅限周三使用",
"不与商户其他优惠同享",
"具体使用细则请以银行和门店公告为准"
]
},
"payment": [
{
"name_en": "",
"id": 169,
"name": "网银快捷支付"
}
],
"small_img_url": null,
"act_type": 1,
"zone_include_id": [
587
],
"banks_enrich": {
"name": "招商",
"id": 11
},
"original_title": "(周三5折)江小鱼江鱼馆100元代金券",
"display_tags": [],
"big_img_url": "https://qnpic.billbear.cn/FriUhDNz2c4KICWvyc-ETd-xk7Gt",
"available_time_ranges": [
{
"range_type": 2,
"start_time": 36000,
"end_time": 86100,
"weekday": 2,
"day": null
}
],
"zone_exclude": [],
"banks": [
{
"id": 11,
"name": "招商银行"
}
],
"title": "江小鱼江鱼馆 100 元代金券 (周三 5 折)",
"is_hot": 0,
"money_limit": null
},
{
"zone_exclude_id": [],
"create_time": "2017-11-29 13:16:43.594000",
"id": 269793,
"subject": {
"type": 1,
"name": "bank"
},
"category": [
{
"name_en": "",
"id": 316,
"name": "中餐"
}
],
"special_dates": [],
"original_url": "https://piao.o2o.cmbchina.com/shopwindow/weixinProductDetail?cityNo=714&productNo=9917112800078",
"special_weekdays": [],
"content": """
##### 参与方式
- 在招行「掌上生活 APP」购劵
- 至「江小鱼江鱼馆」使用
##### 活动内容
- 活动期间,招商银行信用卡持卡人至招行「掌上生活 APP」 80 元购「江小鱼江鱼馆」价值 100 元代金券
##### 注意事项
- 不与商户其他优惠同享
- 酒水饮料、主食除外
- 具体使用细则请以银行和门店公告为准
""",
"usage_scene": [
{
"name_en": "food",
"id": 2,
"name": "餐饮"
}
],
"start_date": "2017-11-29",
"status": 1,
"subject_description": "招商银行信用卡持卡人",
"update_time": "2018-01-02 15:54:00.766000",
"card_orgs": [],
"end_date": "2018-03-31",
"bank_logo_enrich": {
"logo_url": "https://cdn.xhqb.com/bank/picture_xh/11.png"
},
"receive_description": "",
"unavailable_time_ranges": [],
"quota": "数量有限,先到先得",
"zone_include": [
"黄石"
],
"discount": [
{
"name_en": "free_coupon",
"id": 22,
"name": "享券"
}
],
"category_enrich": {
"name": "美食",
"id": 10
},
"act_area": null,
"content_enrich": {
"参与方式": [
"在招行「掌上生活 APP」购劵",
"至「江小鱼江鱼馆」使用"
],
"活动内容": [
"活动期间,招商银行信用卡持卡人至招行「掌上生活 APP」 80 元购「江小鱼江鱼馆」价值 100 元代金券"
],
"注意事项": [
"不与商户其他优惠同享",
"酒水饮料、主食除外",
"具体使用细则请以银行和门店公告为准"
]
},
"payment": [
{
"name_en": "",
"id": 169,
"name": "网银快捷支付"
}
],
"small_img_url": null,
"act_type": 1,
"zone_include_id": [
587
],
"banks_enrich": {
"name": "招商",
"id": 11
},
"original_title": "江小鱼江鱼馆100元代金券",
"display_tags": [],
"big_img_url": "https://qnpic.billbear.cn/FsuJGkUVpxOwCfkrenCZLsrFpxX_",
"available_time_ranges": [
{
"range_type": 1,
"start_time": 0,
"end_time": 86400,
"weekday": null,
"day": null
}
],
"zone_exclude": [],
"banks": [
{
"id": 11,
"name": "招商银行"
}
],
"title": "江小鱼江鱼馆 100 元代金券",
"is_hot": 0,
"money_limit": null
}
],
"label": "have_act",
"brand_logo_enrich": {
"logo_url_lx": "https://cdn.xhqb.com/brand/picture_lx/35572.png",
"logo_url_xh": "https://cdn.xhqb.com/brand/picture_xh/35572.jpg"
},
"create_time": "2018-01-09 14:40:48.846000",
"city_enrich": {
"region": "华中地区",
"name": "黄石",
"level": 4
},
"address": "黄石港区天津路52号十五中对面",
"coordinate": {
"lat": 30.216619,
"lon": 115.08882
},
"id": 351971
}
}
]
}
}