I can't filter nested

I have a document that is in the form of nested data
But I can't filter in a nested way and all the products return a category, while in the query I have only filtered products that have attribute_id 40.

my mapping

{
    "mappings": {
      "properties": {
        "attributes": {
          "properties": {
            "options": {
              "properties": {
                "attribute_id": {
                  "type": "long"
                },
                "created_at": {
                  "type": "long"
                },
                "id": {
                  "type": "long"
                },
                "order": {
                  "type": "long"
                },
                "updated_at": {
                  "type": "long"
                },
                "value": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                }
              }
            },
            "title": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "id": {
          "type": "long"
        },
        "link": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "meta_description": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "meta_keywords": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "meta_title": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "position": {
          "type": "long"
        },
        "products": {
          "type": "nested",
          "properties": {
            "attributes": {
              "type": "nested",
              "properties": {
                "attribute_id": {
                  "type": "integer"
                },
                "product_id": {
                  "type": "integer"
                },
                "title": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "type": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "value": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                }
              }
            },
            "created_at": {
              "type": "date",
              "format": "YYY-MM-dd HH:mm:ss",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "en_name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "id": {
              "type": "integer"
            },
            "link": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "name": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "new": {
              "type": "boolean"
            },
            "new_from_date": {
              "type": "date",
              "format": "YYY-MM-dd HH:mm:ss",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "new_to_date": {
              "type": "date",
              "format": "YYY-MM-dd HH:mm:ss",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "order_count": {
              "type": "integer"
            },
            "price": {
              "type": "float"
            },
            "sku": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "special_from_date": {
              "type": "date",
              "format": "YYY-MM-dd HH:mm:ss",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "special_price": {
              "type": "integer"
            },
            "special_to_date": {
              "type": "date",
              "format": "YYY-MM-dd HH:mm:ss",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "view_count": {
              "type": "integer"
            },
            "whishlist_count": {
              "type": "integer"
            }
          }
        },
        "seo": {
          "properties": {
            "breadcrumbs": {
              "properties": {
                "@type": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "link": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "name": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                },
                "position": {
                  "type": "long"
                },
                "url": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                }
              }
            },
            "metaDescription": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "metaTitle": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "meta_keywords": {
              "properties": {
                "value": {
                  "type": "text",
                  "fields": {
                    "keyword": {
                      "type": "keyword",
                      "ignore_above": 256
                    }
                  }
                }
              }
            },
            "title": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        },
        "sort_order": {
          "type": "long"
        },
        "sortable": {
          "properties": {
            "id": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "title": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            }
          }
        }
      }
    }
}

Category Data

{
  "id" : 5,
  "name" : "mobile",
  "link" : "/category/5/phone",
  "sort_order" : 1,
  "position" : 4,
  "seo" : {
    "title" : "mobile",
    "metaTitle" : "",
    "meta_keywords" : [
      {
        "value" : "phone"
      },
      {
        "value" : "mobile"
      }
    ],
    "metaDescription" : "",
    "description" : null,
    "breadcrumbs" : [
      {
        "@type" : "ListItem",
        "position" : 1,
        "name" : "home",
        "url" : "https://www.atramart.com/"
      },
      {
        "@type" : "ListItem",
        "position" : 2,
        "name" : "digitalproducts",
        "link" : "/category/5/phone"
      },
      {
        "@type" : "ListItem",
        "position" : 3,
        "name" : "phone",
        "link" : "/category/4/phone"
      },
      {
        "@type" : "ListItem",
        "position" : 4,
        "name" : "mobile",
        "link" : "/category/3/mobile"
      }
    ]
  },
  "products" : [
    {
      "id" : 1,
      "name" : "Xiaomi Black Shark 2 Pro Dual SIM SmartPhone",
      "sku" : "010002919100",
      "en_name" : "Xiaomi Black Shark 2 Pro Dual SIM SmartPhone - 128GB(12GB)",
      "new" : true,
      "new_from_date" : "2023-06-29 17:29:51",
      "new_to_date" : "2023-06-29 17:29:51",
      "price" : 4177739.0,
      "special_price" : 0,
      "special_from_date" : "2023-06-29 17:29:51",
      "special_to_date" : "2023-06-29 17:29:51",
      "link" : "/product/am-010002919100/Xiaomi Black Shark 2 Pro Dual SIM SmartPhone",
      "view_count" : 0,
      "whishlist_count" : 0,
      "order_count" : 0,
      "created_at" : "2023-06-29 12:33:58",
      "attributes" : [
        {
          "product_id" : 1,
          "attribute_id" : 26,
          "title" : "is_in_stock",
          "value" : "ins_stock",
          "type" : "select"
        },
        {
          "product_id" : 1,
          "attribute_id" : 40,
          "title" : "sim_count",
          "value" : "three",
          "type" : "select"
        }
      ]
    }
  ],
  "attributes" : [
    {
      "title" : "status",
      "options" : [
        {
          "id" : 3,
          "value" : "disable",
          "order" : 1,
          "attribute_id" : 10,
          "created_at" : "2023-06-29T09:03:37.000000Z",
          "updated_at" : "2023-06-29T09:03:37.000000Z"
        },
        {
          "id" : 4,
          "value" : "enable",
          "order" : 2,
          "attribute_id" : 10,
          "created_at" : "2023-06-29T09:03:37.000000Z",
          "updated_at" : "2023-06-29T09:03:37.000000Z"
        }
      ]
    }
  ],
  "banners" : null,
  "slider" : null,
  "sortable" : [
    {
      "id" : "_1",
      "title" : "new"
    },
    {
      "id" : "_2",
      "title" : "discount"
    },
    {
      "id" : "_3",
      "title" : "sellers"
    },
  ]
}

Query

GET categories/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "id": 5
          }
        },
        {
          "nested": {
            "path": "products",
            "query": {
              "nested": {
                "path": "products.attributes",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "match": {
                          "products.attributes.attribute_id": 40
                        }
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      ]
    }
  }
}

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