Getting Unique Set of nested objects within aggregations

[Assaf_Cohen]

Hello,

to make my question clear i am attaching steps to reproduce:

PUT /test
{
  "mappings": {
    "dynamic_templates": [
      {
        "strings_as_keywords": {
          "match_mapping_type": "string",
          "mapping": {
            "type": "keyword"
          }
        }
      }
    ],
    "properties": {
      "custom_rules" : {
              "type" : "nested"
      }
    }
  }
}

PUT /test/_doc/1
{
  "order_data": {
    "user_id": "user-1",
    "tenant_id": "tenant-id-1",
    "price": 1000,
    "tags": [
      "a",
      "b"
    ],
    "custom_rules": [
      {
        "rule_id": "111",
        "rule_categories": [
          "demo1",
          "demo2"
        ]
      }
    ]
  }
}

PUT /test/_doc/2
{
  "order_data": {
    "user_id": "user-1",
    "tenant_id": "tenant-id-1",
    "price": 500,
    "tags": [
      "a",
      "c"
    ],
    "custom_rules": [
      {
        "rule_id": "111",
        "rule_categories": [
          "demo1",
          "demo2"
        ]
      },
      {
        "rule_id": "222",
        "rule_categories": [
          "demo1",
          "demo3"
        ]
      }
    ]
  }
}

PUT /test/_doc/3
{
  "order_data": {
    "user_id": "user-2",
    "tenant_id": "tenant-id-1",
    "price": 50,
    "tags": [
      "a",
      "b"
    ],
    "custom_rules": [
      {
        "rule_id": "111",
        "rule_categories": [
          "demo1",
          "demo2"
        ]
      }
    ]
  }
}



GET test/_search?size=0
{
  "aggs": {
    "tenant": {
      "terms": {
        "field": "order_data.tenant_id",
        "size": 10
      },
      "aggs": {
        "user": {
          "terms": {
            "field": "order_data.user_id",
            "size": 10
          },
          "aggs": {
            "price": {
              "sum": {
                "field": "order_data.price"
              }
            },
            "tags": {
              "terms": {
                "field": "order_data.tags",
                "size": 10
              }
            },
            "custom_rules": {
              "terms": {
                "field": "order_data.custom_rules",
                "size": 10
              }
            }
          }
        }
      }
    }
  }
}

For each user i get the sum of all price fields, and list of unique tag names. This works as expected in the query above.

I would also like to get the the "unique" set (i.e. something like Hashset in java) of custom rules for each user. So for example, for user-1 I would like to get:

"custom_rules": [
      {
        "rule_id": "111",
        "rule_categories": [
          "demo1",
          "demo2"
        ]
      },
      {
        "rule_id": "222",
        "rule_categories": [
          "demo1",
          "demo3"
        ]
      }
    ] 

The query i pasted above is not returning any custom_rules.
How can i accomplish this?
your guidance is much appreciated

[RabBit_BR (andre.coelho)]

I tried this way:

{
  "aggs": {
    "tenant": {
      "terms": {
        "field": "order_data.tenant_id",
        "size": 10
      },
      "aggs": {
        "user": {
          "terms": {
            "field": "order_data.user_id",
            "size": 10
          },
          "aggs": {
            "custom_rules_rule_id": {
              "terms": {
                "field": "order_data.custom_rules.rule_id",
                "size": 10
              },
              "aggs": {
                "custom_rules_rule_categories": {
                  "terms": {
                    "field": "order_data.custom_rules.rule_categories",
                    "size": 10
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Results:

"aggregations" : {
    "tenant" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "tenant-id-1",
          "doc_count" : 3,
          "user" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "user-1",
                "doc_count" : 2,
                "custom_rules_rule_id" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "111",
                      "doc_count" : 2,
                      "custom_rules_rule_categories" : {
                        "doc_count_error_upper_bound" : 0,
                        "sum_other_doc_count" : 0,
                        "buckets" : [
                          {
                            "key" : "demo1",
                            "doc_count" : 2
                          },
                          {
                            "key" : "demo2",
                            "doc_count" : 2
                          },
                          {
                            "key" : "demo3",
                            "doc_count" : 1
                          }
                        ]
                      }
                    },
                    {
                      "key" : "222",
                      "doc_count" : 1,
                      "custom_rules_rule_categories" : {
                        "doc_count_error_upper_bound" : 0,
                        "sum_other_doc_count" : 0,
                        "buckets" : [
                          {
                            "key" : "demo1",
                            "doc_count" : 1
                          },
                          {
                            "key" : "demo2",
                            "doc_count" : 1
                          },
                          {
                            "key" : "demo3",
                            "doc_count" : 1
                          }
                        ]
                      }
                    }
                  ]
                }
              },
              {
                "key" : "user-2",
                "doc_count" : 1,
                "custom_rules_rule_id" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "111",
                      "doc_count" : 1,
                      "custom_rules_rule_categories" : {
                        "doc_count_error_upper_bound" : 0,
                        "sum_other_doc_count" : 0,
                        "buckets" : [
                          {
                            "key" : "demo1",
                            "doc_count" : 1
                          },
                          {
                            "key" : "demo2",
                            "doc_count" : 1
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }