常用查询 - ES.md 14 KB
Edit Raw Blame History


======================================
租户相关
======================================
1. 根据 tenant_id / spu_id 查询
curl -u 'essa:4hOaLaf41y2VuI8y'   -X GET 'http://localhost:9200/search_products/_search?pretty'   -H 'Content-Type: application/json'   -d '{
    "size": 1,
    "query": {
      "bool": {
        "filter": [
          { "term": { "tenant_id": "170" } }
        ]
      }
    }
  }'

curl -u 'essa:4hOaLaf41y2VuI8y'   -X GET 'http://localhost:9200/search_products/_search?pretty'   -H 'Content-Type: application/json'   -d '{
    "size": 1,
    "source": ["_id", "*"],
    "query": {
      "bool": {
        "must": [
          {
            "match": {
              "title_zh": {
                "query": "裙子"
              }
            }
          }
        ],
        "filter": [
          { "term": { "tenantid": "170" } }
        ]
      }
    }
}'

curl -u 'essa:4hOaLaf41y2VuI8y'   -X GET 'http://localhost:9200/search_products/_analyze'   -H 'Content-Type: application/json'   -d '{
  "analyzer": "query_ansj",
  "text": "14寸第4代-眼珠实身冰雪公仔带手动大推车,搪胶雪宝宝"
}'

curl -u 'essa:4hOaLaf41y2VuI8y'   -X GET 'http://localhost:9200/search_products/_analyze'   -H 'Content-Type: application/json'   -d '{
  "analyzer": "hanlp_standard",
  "text": "14寸第4代-眼珠实身冰雪公仔带手动大推车,搪胶雪宝宝"
}'

curl -u 'essa:4hOaLaf41y2VuI8y'   -X GET 'http://localhost:9200/search_products/_search?pretty'   -H 'Content-Type: application/json'   -d '{
  "size": 100,
  "from": 0,
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "name": "base_query",
            "fields": [
              "title_zh^3.0",
              "brief_zh^1.5",
              "description_zh",
              "vendor_zh^1.5",
              "tags",
              "category_path_zh^1.5",
              "category_name_zh^1.5",
              "option1_values^0.5"
            ],
            "minimumshould_match": "75%",
            "operator": "AND",
            "query": "裙",
            "tie_breaker": 0.9
          }
        }
      ],
      "filter": [
        {
          "term": {
            "tenant_id": "170"
          }
        }
      ]
    }
  }
}'

curl -u 'essa:4hOaLaf41y2VuI8y'   -X GET 'http://localhost:9200/search_products/_search?pretty'   -H 'Content-Type: application/json'   -d '{
  "size": 1,
  "from": 0,
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "name": "base_query",
            "fields": [
              "title_zh^3.0",
              "brief_zh^1.5",
              "description_zh",
              "vendor_zh^1.5",
              "tags",
              "category_path_zh^1.5",
              "category_name_zh^1.5",
              "option1_values^0.5"
            ],
            "minimum_should_match": "75%",
            "operator": "AND",
            "query": "裙",
            "tie_breaker": 0.9
          }
        }
      ],
      "filter": [
        {
          "term": {
            "tenant_id": "170"
          }
        }
      ]
    }
  },
  "aggs": {
    "category1_name_facet": {
      "terms": {
        "field": "category1_name",
        "size": 15,
        "order": {
          "count": "desc"
        }
      }
    },
    "specifications_color_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filter_by_name": {
          "filter": {
            "term": {
              "specifications.name": "color"
            }
          },
          "aggs": {
            "valuecounts": {
              "terms": {
                "field": "specifications.value",
                "size": 20,
                "order": {
                  "count": "desc"
                }
              }
            }
          }
        }
      }
    },
    "specifications_size_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filter_by_name": {
          "filter": {
            "term": {
              "specifications.name": "size"
            }
          },
          "aggs": {
            "value_counts": {
              "terms": {
                "field": "specifications.value",
                "size": 15,
                "order": {
                  "count": "desc"
                }
              }
            }
          }
        }
      }
    },
    "specifications_material_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filter_by_name": {
          "filter": {
            "term": {
              "specifications.name": "material"
            }
          },
          "aggs": {
            "valuecounts": {
              "terms": {
                "field": "specifications.value",
                "size": 10,
                "order": {
                  "_count": "desc"
                }
              }
            }
          }
        }
      }
    }
  }
}'

GET /search_products/search
{
  "query": {
    "term": {
      "tenantid": "2"
    }
  }
}

curl -u 'essa:4hOaLaf41y2VuI8y'   -X GET 'http://localhost:9200/search_products/_search?pretty'   -H 'Content-Type: application/json'   -d '{
    "size": 5,
    "query": {
      "bool": {
        "filter": [
          { "term": { "spu_id": "74123" } }
        ]
      }
    }
  }'
2. 统计租户的总文档数
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_count?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  }
}'
======================================
分面数据诊断相关查询
======================================
1. 检查ES文档的分面字段数据
1.1 查询特定租户的商品，显示分面相关字段
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  },
  "size": 1,
  "source": [
    "spu_id", 
    "title_zh", 
    "category1_name", 
    "category2_name",
    "category3name",
    "specifications", 
    "option1_name",
    "option2_name",
    "option3_name"
  ]
}'
1.2 验证category1_name字段是否有数据
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "162" } },
        { "exists": { "field": "category1_name" } }
      ]
    }
  },
  "size": 0
}'
1.3 验证specifications字段是否有数据
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "162" } },
        { "exists": { "field": "specifications" } }
      ]
    }
  },
  "size": 0
}'
2. 分面聚合查询（Facet Aggregations）
2.1 category1_name 分面聚合
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  },
  "size": 0,
  "aggs": {
    "category1_name_facet": {
      "terms": {
        "field": "category1_name.keyword",
        "size": 50
      }
    }
  }
}'
2.2 specifications.color 分面聚合
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  },
  "size": 0,
  "aggs": {
    "specifications_color_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filtered": {
          "filter": {
            "term": {
              "specifications.name": "color"
            }
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "specifications.value.keyword",
                "size": 50
              }
            }
          }
        }
      }
    }
  }
}'
2.3 specifications.size 分面聚合
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  },
  "size": 0,
  "aggs": {
    "specifications_size_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filtered": {
          "filter": {
            "term": {
              "specifications.name": "size"
            }
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "specifications.value.keyword",
                "size": 50
              }
            }
          }
        }
      }
    }
  }
}'
2.4 specifications.material 分面聚合
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  },
  "size": 0,
  "aggs": {
    "specifications_material_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filtered": {
          "filter": {
            "term": {
              "specifications.name": "material"
            }
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "specifications.value.keyword",
                "size": 50
              }
            }
          }
        }
      }
    }
  }
}'
2.5 综合分面聚合（category + color + size + material）
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  },
  "size": 0,
  "aggs": {
    "category1_name_facet": {
      "terms": {
        "field": "category1_name.keyword",
        "size": 50
      }
    },
    "specifications_color_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filtered": {
          "filter": {
            "term": {
              "specifications.name": "color"
            }
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "specifications.value.keyword",
                "size": 50
              }
            }
          }
        }
      }
    },
    "specifications_size_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filtered": {
          "filter": {
            "term": {
              "specifications.name": "size"
            }
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "specifications.value.keyword",
                "size": 50
              }
            }
          }
        }
      }
    },
    "specifications_material_facet": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "filtered": {
          "filter": {
            "term": {
              "specifications.name": "material"
            }
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "specifications.value.keyword",
                "size": 50
              }
            }
          }
        }
      }
    }
  }
}'
3. 检查specifications嵌套字段的详细结构
3.1 查看specifications的name字段有哪些值
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "term": {
      "tenant_id": "162"
    }
  },
  "size": 0,
  "aggs": {
    "specifications_names": {
      "nested": {
        "path": "specifications"
      },
      "aggs": {
        "name_values": {
          "terms": {
            "field": "specifications.name",
            "size": 20
          }
        }
      }
    }
  }
}'
3.2 查看某个商品的完整specifications数据
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "162" } },
        { "exists": { "field": "specifications" } }
      ]
    }
  },
  "size": 1,
  "source": ["spu_id", "titlezh", "specifications"]
}'
4. 统计查询
4.1 统计有category1_name的文档数量
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_count?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "162" } },
        { "exists": { "field": "category1_name" } }
      ]
    }
  }
}'
4.2 统计有specifications的文档数量
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_count?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "162" } },
        { "exists": { "field": "specifications" } }
      ]
    }
  }
}'
5. 诊断问题场景
5.1 查找没有category1_name但有category的文档（MySQL有数据但ES没有）
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "162" } }
      ],
      "must_not": [
        { "exists": { "field": "category1_name" } }
      ]
    }
  },
  "size": 10,
  "source": ["spu_id", "title_zh", "categoryname_zh", "category_path_zh"]
}'
5.2 查找有option但没有specifications的文档（数据转换问题）
curl -u 'essa:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "162" } },
        { "exists": { "field": "option1_name" } }
      ],
      "must_not": [
        { "exists": { "field": "specifications" } }
      ]
    }
  },
  "size": 10,
  "source": ["spu_id", "title_zh", "option1_name", "option2name", "option3_name", "specifications"]
}'