# 关于多层nested 嵌套查询(多规格商品查询),数据查询要怎么做

**URL:** <https://discuss.elastic.co/t/nested/281930>\
**Category:** 中文提问与讨论\
**Created:** [August 19, 2021, 8:30am UTC](https://discuss.elastic.co/t/nested/281930 "2021-08-19T08:30:32Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![XuShall](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xushall/32/93411_2.png) [@XuShall](https://discuss.elastic.co/u/XuShall)\
**Post date:** [August 19, 2021, 8:30am UTC](https://discuss.elastic.co/t/nested/281930/1 "2021-08-19T08:30:32Z")

</div>

关于多层nested 嵌套查询(多规格商品查询),数据查询要怎么做

结构

```bash
PUT goods
{
	"mappings": {
		"properties": {
			"spu_id": {
				"type": "integer"
			},
			"title": {
				"type": "text",
				"analyzer": "ik_smart"
			},
			"sub_title": {
				"type": "text",
				"analyzer": "ik_smart"
			},
			"cid1": {
				"type": "integer"
			},
			"cid2": {
				"type": "integer"
			},
			"cid3": {
				"type": "integer"
			},
			"brand_id": {
				"type": "integer"
			},
			"saleable": {
				"type": "boolean"
			},
			"valid": {
				"type": "boolean"
			},
			"description": {
				"type": "text",
				"analyzer": "ik_smart"
			},
			"spu_spec": {
				"type": "nested",
				"properties": {
					"key": {
						"type": "keyword"
					},
					"value": {
						"type": "keyword"
					},
					"search_key_value": {
						"type": "keyword",
						"copy_to": "spus_spec"
					}
				}
			},
			"skus": {
				"type": "nested",
				"properties": {
					"spu_id": {
						"type": "integer",
						"copy_to": "spus_id"
					},
					"sku_id": {
						"type": "integer",
						"copy_to": "skus_id"
					},
					"title": {
						"type": "text",
						"analyzer": "ik_smart",
						"copy_to": "skus_title"
					},
					"price": {
						"type": "scaled_float",
						"scaling_factor": 100,
						"copy_to": "skus_price"
					},
					"enable": {
						"type": "boolean",
						"copy_to": "skus_enable"
					},
					"stock": {
						"type": "integer",
						"copy_to": "skus_stock"
					},
					"use_search_key_value": {
						"type": "text",
						"analyzer": "ik_smart",
						"copy_to": "use_skus_spec"
					},
					"sku_spec": {
						"type": "nested",
						"properties": {
							"key": {
								"type": "keyword"
							},
							"value": {
								"type": "keyword"
							},
							"search_key_value": {
								"type": "keyword",
								"copy_to": "skus_spec"
							}
						}
					}
				}
			}
		}
	}
}

```

数据

```bash
POST _bulk
{"index":{"_index":"goods","_id":1}}
{"title":"华为p40","sub_title":"华为p40手机","cid1":1,"cid2":2,"cid3":3,"brand_id":1,"saleable":true,"valid":true,"spu_id":1,"spu_spec":[{"key":"cpu品牌","value":"高通骁龙","search_key_value":"cpu品牌:高通骁龙"},{"key":"cpu核数","value":"4核","search_key_value":"cpu核数:4核"},{"key":"cpu频率","value":"3.3GHz","search_key_value":"cpu频率:3.3GHz"}],"skus":[{"spu_id":1,"sku_id":1,"title":"华为p40 pro 512gb","price":2000,"enable":true,"sku_spec":[{"key":"机身存储","value":"512gb","search_key_value":"机身存储:512gb"},{"key":"内存","value":"16gb","search_key_value":"内存:16gb"}],"use_search_key_value":"机身存储:512gb|内存:16gb|"},{"spu_id":1,"sku_id":2,"title":"华为p40 pro 256gb","price":1500,"enable":true,"sku_spec":[{"key":"机身存储","value":"256gb","search_key_value":"机身存储:256gb"},{"key":"内存","value":"16gb","search_key_value":"内存:16gb"}],"use_search_key_value":"机身存储:256gb|内存:16gb|"},{"spu_id":1,"sku_id":3,"title":"华为p40 256gb","price":1000,"enable":true,"sku_spec":[{"key":"机身存储","value":"256gb","search_key_value":"机身存储:256gb"},{"key":"内存","value":"8gb","search_key_value":"内存:8gb"}],"use_search_key_value":"机身存储:256gb|内存:8gb|"}]}
{"index":{"_index":"goods","_id":2}}
{"title":"iphone12","sub_title":"iphone12","cid1":1,"cid2":2,"cid3":3,"brand_id":2,"saleable":true,"valid":true,"spu_id":2,"spu_spec":[{"key":"cpu品牌","value":"苹果","search_key_value":"cpu品牌:苹果"},{"key":"cpu核数","value":"8核","search_key_value":"cpu核数:8核"},{"key":"cpu频率","value":"4.3GHz","search_key_value":"cpu频率:4.3GHz"}],"skus":[{"spu_id":2,"sku_id":4,"title":"iphone12Pro","price":3000,"enable":true,"sku_spec":[{"key":"机身存储","value":"128gb","search_key_value":"机身存储:128gb"},{"key":"内存","value":"8gb","search_key_value":"内存:8gb"},{"key":"屏幕大小","value":"4.9寸","search_key_value":"屏幕大小:4.9寸"}],"use_search_key_value":"机身存储:128gb|内存:8gb|屏幕大小:4.9寸|"},{"spu_id":2,"sku_id":5,"title":"iphone12 ProMaz","price":4000,"enable":true,"sku_spec":[{"key":"机身存储","value":"256gb","search_key_value":"机身存储:256gb"},{"key":"内存","value":"16gb","search_key_value":"内存:16gb"},{"key":"屏幕大小","value":"5.5寸","search_key_value":"屏幕大小:5.5寸"}],"use_search_key_value":"机身存储:256gb|内存:16gb|屏幕大小:5.5寸|"}]}

```

查询

```bash
POST goods/_search
{"query":{"bool":{"filter":[{"term":{"saleable":true}},{"term":{"valid":true}},{"nested":{"path":"spu_spec","query":[{"term":{"spu_spec.key":"cpu品牌"}},{"term":{"spu_spec.value":"苹果"}}]}},{"nested":{"path":"spu_spec","query":[{"term":{"spu_spec.key":"cpu核数"}},{"term":{"spu_spec.value":"8核"}}]}},{"nested":{"path":"skus","query":{"nested":{"path":"skus.sku_spec","query":[{"term":{"skus.sku_spec.search_key_value":"内存:8gb"}}]}}}},{"nested":{"path":"skus","query":{"nested":{"path":"skus.sku_spec","query":[{"term":{"skus.sku_spec.search_key_value":"机身存储:256gb"}}]}}}}]}},"from":0,"size":100,"aggs":{"fenmian_search":{"nested":{"path":"spu_spec"},"aggs":{"fenmian_search":{"terms":{"field":"spu_spec.key"},"aggs":{"value":{"terms":{"field":"spu_spec.value"}}}}}},"part_search":{"nested":{"path":"skus"},"aggs":{"part_search":{"nested":{"path":"sku_spec"},"aggs":{"part_search":{"terms":{"field":"skus.sku_spec.key"},"aggs":{"value":{"terms":{"field":"skus.sku_spec.value"}}}}}}}}}}

```

 ![Xnip2021-08-19_14-02-11](https://us1.discourse-cdn.com/elastic/original/3X/0/5/05bb213cfa2c3f50ffe16b2e945b7f5117d2c5cb.png)

存储的值是[内存:8,机身存储:128],[内存:16,机身存储:256].搜索内存8,机身存储256 也会搜索到.

---

<div class="post-metadata">

**Author:** ![joecwu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joecwu/32/37258_2.png) [@joecwu](https://discuss.elastic.co/u/joecwu)\
**Post date:** [September 8, 2021, 6:39pm UTC](https://discuss.elastic.co/t/nested/281930/2 "2021-09-08T18:39:12Z")

</div>

> **[Nested query | Elasticsearch Guide \[7.14\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html)**

需依照 nested query 的範例，先使用 nested type 來宣告 mapping，並且使用 nested query 來進行查詢，因為 lucene 並沒有 array of object 的這種結構，所以預設 elasticsearch 會將 array of object 給攤平 (flatten)，因此會出現你提到的情況。

---

<div class="post-metadata">

**Author:** ![XuShall](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xushall/32/93411_2.png) [@XuShall](https://discuss.elastic.co/u/XuShall)\
**Post date:** [September 9, 2021, 9:50am UTC](https://discuss.elastic.co/t/nested/281930/3 "2021-09-09T09:50:53Z")

</div>

感谢回复!我对于elasticsearch还是个新手,有了解到您所说的摊平这种结构.请问目前我这种情况有什么好用的替代方案嘛?

---

<div class="post-metadata">

**Author:** ![joecwu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joecwu/32/37258_2.png) [@joecwu](https://discuss.elastic.co/u/joecwu)\
**Post date:** [September 9, 2021, 12:07pm UTC](https://discuss.elastic.co/t/nested/281930/4 "2021-09-09T12:07:05Z")

</div>

你的第二層的 nested obj 裡面，要先用 nested query 包起來，裡面再進行第二層內部的查詢，這時第二層也要用 bool ，就再裡面再做一次獨立的 bool + nested query 即可。

```auto
POST goods/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "saleable": true
          }
        },
        {
          "term": {
            "valid": true
          }
        },
        {
          "nested": {
            "path": "spu_spec",
            "query": [
              {
                "term": {
                  "spu_spec.key": "cpu品牌"
                }
              },
              {
                "term": {
                  "spu_spec.value": "苹果"
                }
              }
            ]
          }
        },
        {
          "nested": {
            "path": "spu_spec",
            "query": [
              {
                "term": {
                  "spu_spec.key": "cpu核数"
                }
              },
              {
                "term": {
                  "spu_spec.value": "8核"
                }
              }
            ]
          }
        },
        {
          "nested": {
            "path": "skus",
            "query": {
              "bool": {
                "filter": [
                  {
                    "nested": {
                      "path": "skus.sku_spec",
                      "query": {
                        "term": {
                          "skus.sku_spec.search_key_value": "内存:8gb"
                        }
                      }
                    }
                  },
                  {
                    "nested": {
                      "path": "skus.sku_spec",
                      "query": {
                        "term": {
                          "skus.sku_spec.search_key_value": "机身存储:128gb"
                        }
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 100,
  "aggs": {
    "fenmian_search": {
      "nested": {
        "path": "spu_spec"
      },
      "aggs": {
        "fenmian_search": {
          "terms": {
            "field": "spu_spec.key"
          },
          "aggs": {
            "value": {
              "terms": {
                "field": "spu_spec.value"
              }
            }
          }
        }
      }
    },
    "part_search": {
      "nested": {
        "path": "skus"
      },
      "aggs": {
        "part_search": {
          "nested": {
            "path": "sku_spec"
          },
          "aggs": {
            "part_search": {
              "terms": {
                "field": "skus.sku_spec.key"
              },
              "aggs": {
                "value": {
                  "terms": {
                    "field": "skus.sku_spec.value"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![XuShall](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xushall/32/93411_2.png) [@XuShall](https://discuss.elastic.co/u/XuShall)\
**Post date:** [September 10, 2021, 1:07am UTC](https://discuss.elastic.co/t/nested/281930/5 "2021-09-10T01:07:30Z")

</div>

再次感谢! 😬

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 8, 2021, 1:08am UTC](https://discuss.elastic.co/t/nested/281930/6 "2021-10-08T01:08:03Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.