[已解决]elasticsearch,关于多层nested 嵌套查询(多规格商品查询),数据查询要怎么做

关于多层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 也会搜索到.

ShiKi
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
ShiKi
最佳答案
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": "内存:16gb"
                        }
                      }
                    }
                  },
                  {
                    "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"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
2年前 评论
讨论数量: 3

有更多相关es精华吗?

2年前 评论

skus.sku_spec.search_key_value 等同于同一字段 存储两种属性 ,内存:8, 机身存储:128;
想让同一字段上的值既等于<内存:8>又等于<机身存储:128>,并且这个字段的值是字符串类型,应该是做不到的

2年前 评论
ShiKi
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": "内存:16gb"
                        }
                      }
                    }
                  },
                  {
                    "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"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
2年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!