使用 group by 查询所有字段值的 Elasticsearch 查询

分享于2022年07月17日 elasticsearch elasticsearch-aggregation 问答
【问题标题】:使用 group by 查询所有字段值的 Elasticsearch 查询(Elasticsearch query for all values of field with group by)
【发布时间】:2022-01-26 09:56:08
【问题描述】:

我在形成查询以按某种方式使用 sql 组获取所有值时遇到问题。

所以下面是我的数据结构:

product index:

{
  "createdBy" : "61c1fcdd88dbad1920da8caf",
  "creationTime" : "2021-12-22T11:58:53.576932Z",
  "lastModifiedBy" : "61c1fcdd88dbad1920da8caf",
  "lastModificationTime" : "2021-12-22T11:58:53.576932Z",
  "id" : "61c312fdc6aa620a609db0b2",
  "title" : "string",
  "brand" : "string",
  "longDesc" : "string",
  "categoryId" : "string",
  "imageUrls" : [
    "string",
    "string"
  ],
  "keySpecs" : [
    "string",
    "string",
  ],
  "facets" : [
    {
      "name" : "color",
      "value" : "red"
    },
    {
      "name" : "storage",
      "value" : "16 GB"
    },
    {
      "name" : "brand",
      "value" : "Intex"
    }
  ],
  "categoryName" : "handsets"
}

现在,我想获取具有不同值和计数的所有方面。比方说

  1. productA 为蓝色,productB 为红色
  2. productA 有品牌 ABC,productB 有品牌 XYZ

所以,我想要列出所有方面的数据,例如:

  1. 颜色:蓝色(200支),红色(12支)
  2. 品牌:ABC(13支)、XYZ(99支)

另外,不同的产品会有不同的刻面类型,比如 iphone 会有颜色记忆品牌尺寸,但一支笔只有颜色和品牌(不是记忆/尺寸)。

注意:我使用的是最新版本的 elastic

==================

更新 1 : 下面是es映射详情

{
  "settings": {
    "analysis": {
      "filter": {
        "english_stop": {
          "type": "stop",
          "stopwords": "_english_"
        },
        "english_keywords": {
          "type": "keyword_marker",
          "keywords": [
            "example"
          ]
        },
        "english_stemmer": {
          "type": "stemmer",
          "language": "english"
        },
        "english_possessive_stemmer": {
          "type": "stemmer",
          "language": "possessive_english"
        }
      },
      "analyzer": {
        "lalashree_standard_analyzer": {
          "tokenizer": "standard",
          "filter": [
            "english_possessive_stemmer",
            "lowercase",
            "english_stop",
            "english_keywords",
            "english_stemmer"
          ]
        },
        "html_standard_analyzer": {
          "char_filter": [
            "html_strip"
          ],
          "tokenizer": "standard",
          "filter": [
            "english_possessive_stemmer",
            "lowercase",
            "english_stop",
            "english_keywords",
            "english_stemmer"
          ]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
      },
      "createdBy": {
        "type": "keyword"
      },
      "creationTime": {
        "type": "date"
      },
      "lastModifiedBy": {
        "type": "keyword"
      },
      "lastModificationTime": {
        "type": "date"
      },
      "deleted": {
        "type": "boolean"
      },
      "deletedBy": {
        "type": "keyword"
      },
      "deletionTime": {
        "type": "date"
      },

      "title": {
        "type": "text",
        "analyzer": "lalashree_standard_analyzer",
        "fields": {
          "suggest": {
            "type": "completion"
          }
        }
      },
      "shortDesc": {
        "type": "text",
        "analyzer": "lalashree_standard_analyzer"
      },
      "longDesc": {
        "type": "text",
        "analyzer": "lalashree_standard_analyzer"
      },
      "categoryId": {
        "type": "keyword"
      },
      "searchDetails": {
        "type": "object",
        "properties": {
          "desc": {
            "type": "text",
            "analyzer": "lalashree_standard_analyzer"
          },
          "keywords": {
            "type": "text",
            "analyzer": "lalashree_standard_analyzer",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      },
      "imageUrls": {
        "type": "keyword",
        "index": false
      },
      "keySpecs": {
        "type": "text",
        "analyzer": "lalashree_standard_analyzer"
      },
      "sections": {
        "type": "object",
        "properties": {
          "name": {
            "type": "text",
            "index": false
          },
          "shortDesc": {
            "type": "text",
            "analyzer": "lalashree_standard_analyzer"
          },
          "longDesc": {
            "type": "text",
            "analyzer": "lalashree_standard_analyzer"
          },
          "htmlContent": {
            "type": "text",
            "analyzer": "html_standard_analyzer"
          }
        }
      },
      "facets": {
        "type": "nested",
        "properties": {
          "name": {
            "type": "keyword"
          },
          "value": {
            "type": "keyword"
          }
        }
      },
      "specificationItems": {
        "type": "object",
        "properties": {
          "key": {
            "type": "text",
            "analyzer": "lalashree_standard_analyzer",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "values": {
            "type": "text",
            "analyzer": "lalashree_standard_analyzer"
          }
        }
      },
      "categoryName": {
        "type": "keyword"
      },
      "productFamily": {
        "type": "nested",
        "properties": {
          "id": {
            "type": "keyword"
          },
          "familyVariantOptions": {
            "type": "nested",
            "properties": {
              "name": {
                "type": "keyword"
              },
              "values": {
                "type": "keyword"
              }
            }
          },
          "productFamilyItems": {
            "type": "nested",
            "properties": {
              "baseProductId": {
                "type": "keyword"
              },
              "itemVariantInfoSet": {
                "type": "nested",
                "properties": {
                  "name": {
                    "type": "keyword"
                  },
                  "value": {
                    "type": "keyword"
                  }
                }
              }
            }
          }
        }
      },
      "rating": {
        "type": "float"
      },
      "totalReviewsCount": {
        "type": "long"
      },
      "stores": {
        "type": "nested",
        "properties": {
          "id": {
            "type": "keyword"
          },
          "logo": {
            "type": "keyword",
            "index": false
          },
          "active": {
            "type": "boolean"
          },
          "name": {
            "type": "text"
          },
          "quantity": {
            "type": "long"
          },
          "rating": {
            "type": "float"
          },
          "totalReviewsCount": {
            "type": "long"
          },
          "price.mrp": {
            "type": "float"
          },
          "price.sp": {
            "type": "float"
          },
          "location.geoPoint": {
            "type": "geo_point"
          },
          "oos": {
            "type": "boolean"
          }
        }
      }
    }
  }
}

  • 可以分享 facets 字段的映射吗?是嵌套的吗?
  • @Val 是的,已更新相关映射详细信息
  • 您是否尝试将构面拉入自己的领域,例如 "facet_color" : "red","facet_storage" : "16 GB","facet_brand" : "Intex" ?然后查询将变得微不足道。

【解决方案1】:

此查询首先按名称分组,然后对每个名称的值进行分组。通过设置大小,您可以排列所需的构面数量以及每个构面中的项目数。我认为它可以满足您的需求。

请注意,如果您的文档过多并且性能很重要,则此查询可能会执行不良。

{
  "size": 0, 
  "aggs": {
    "facets": {
      "nested": {
        "path": "facets"
      },
      "aggs": {
        "names": {
          "terms": {
            "field": "facets.name",
            "size": 10
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "facets.value",
                "size": 10
              }
            }
          }
        }
      }
    }
  }
}

  • 感谢这有效,字段名称有小错误。 ``` GET /product/_search { "aggs": { "facets": { "nested": { "path": "facets" }, "aggs": { "names": { "terms": { "field ": "facets.name", "size": 20 }, "aggs": { "values": { "terms": { "field": "facets.value", "size": 50 } } } } } } },“大小”:0 }