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

[XuShall (Shiki)]

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

结构

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"
							}
						}
					}
				}
			}
		}
	}
}

数据

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寸|"}]}

查询

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"}}}}}}}}}}

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

[joecwu (Joe Wu)]

Nested query | Elasticsearch Guide [7.14] | Elastic

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

[XuShall (Shiki)]

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

[joecwu (Joe Wu)]

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

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"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

[XuShall (Shiki)]

再次感谢!