Searching on aggregated nested fields

Elastic Stack Elasticsearch
1

Hi Team Elastic,

We have a requirement where we created continuous transform to keep track of field status of our trades data. The transform aggregates the data and produces documents in this form -

{
        "_index": "tradebystatusarm1",
        "_id": "NgA3KmZlOSFTcdVfchLP9eGjAAAAAAAA",
        "_score": 1,
        "_source": {
          "tradeDateTime": "2022-07-08T00:00:00.000Z",
          "regulator": {
            "BaFIN": {
              "assetclass": {
                "EQUI": {
                  "accountName": {
                    "ACCOUNT1": {
                      "statuss": {
                        "NEW": {
                          "count": 1,
                          "tm": {
                            "submissionAccountName.keyword": "ACCOUNT1",
                            "assetClass.keyword": "EQUI",
                            "regulator.keyword": "BaFIN",
                            "status.keyword": "NEW"
                          }
                        },
                        "UNR": {
                          "count": 1,
                          "tm": {
                            "submissionAccountName.keyword": "ACCOUNT1",
                            "assetClass.keyword": "EQUI",
                            "regulator.keyword": "BaFIN",
                            "status.keyword": "UNR"
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          },
          "executingEntityIdCode": "635400BDQCJNMOGTBB61"
        }
      },
      {
        "_index": "tradebystatusarm1",
        "_id": "NgBEtCHrC8H1qJoQ8kyg9CGVAAAAAAAA",
        "_score": 1,
        "_source": {
          "tradeDateTime": "2022-07-10T00:00:00.000Z",
          "regulator": {
            "BaFIN": {
              "assetclass": {
                "EQUI": {
                  "accountName": {
                    "ACCOUNT1": {
                      "statuss": {
                        "RPD": {
                          "count": 1,
                          "tm": {
                            "submissionAccountName.keyword": "ACCOUNT1",
                            "assetClass.keyword": "EQUI",
                            "regulator.keyword": "BaFIN",
                            "status.keyword": "RPD"
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          },
          "executingEntityIdCode": "635400BDQCJNMOGTBB61"
        }
      }

Now this might be very obvious for experts, but I am not able to get my head around as to how to query this data ? For example, for executingEntityIdCode": "635400BDQCJNMOGTBB61", ACCOUNT1, what's the unique status count?

I understand there might be another aggregation required on top of it. But I can't seemt o get the syntax for the fillers in between that are actual values from document.

2

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