Store query in cache

Hi, how to put query with aggregation in ram cache? I can't find an example.
Version: 7.9
There is my query:

GET product/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must_not": {
        "match": {
          "blocked": true
        }
      },
      "should": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "bool": {
                "filter": {
                  "bool": {
                    "should": [
                      {
                        "match": {
                          "categories.id": "CAT1"
                        }
                      },
                      {
                        "match": {
                          "categories.id": "CAT2"
                        }
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "categories": {
      "nested": {
        "path": "categories"
      },
      "aggs": {
        "category_name": {
          "terms": {
            "field": "categories.id.keyword",
            "min_doc_count": 1,
            "size": 500,
            "order": {
              "_count": "desc"
            }
          },
          "aggs": {
            "category_products": {
              "reverse_nested": {},
              "aggs": {
                "product": {
                  "top_hits": {
                    "size": 10,
                    "sort": {
                      "noOfSales": "desc"
                    },
                    "_source": [
                      "id"
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Elasticsearch will cache commonly accessed queries automatically, what issue are you trying to solve with this?

This is EOL, please upgrade.

This query should be in cache, but response time is 3s. How can I know if a query is in the cache?

Node query cache settings | Elasticsearch Guide [8.5] | Elastic is a starting point on this. I am not sure if there is a specific API endpoint to see if something is cached though sorry.

If your query is slow, it might be worth posting some more context on it and we might be able to share some suggestions.

This is aggs:

  "aggs": {
    "categories": {
      "nested": {
        "path": "categories"
      },
      "aggs": {
        "category_name": {
          "terms": {
            "field": "categories.categoryId.keyword",
            "min_doc_count": 1,
            "size": 600,
            "order": {
              "_count": "desc"
            }
          },
      "aggs": {
        "category_products": {
          "reverse_nested": {},
          "aggs": {
            "product": {
              "top_hits": {
                "size": 1,
                "sort": {
                  "noOfSales": "desc"
                },
                    "_source": [
                      "id",
                      "noOfSales",
                      "assets",
                      "inventory",
                      "categories"
                    ]
              }
            }
          }
            }
          }
        }
      }
    }
  }

last aggs (category_products) is very slow, 3 min is request time,

     "aggs": {
          "category_products": {
            "reverse_nested": {},
            "aggs": {
              "product": {
                "top_hits": {
                  "size": 1,
                  "sort": {
                    "noOfSales": "desc"
                  },
                    "_source": [
                      "id",
                      "noOfSales",
                      "assets",
                      "inventory",
                      "categories"
                    ]
                }
              }
            }

I try to get most sales product in category and display categories whit most sales product. I sort all products in buckets and take them first,. Is there a better, faster way?

Doc count error upper bound is 13, by buckets 4.

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