# Aggregating fields within document

**URL:** <https://discuss.elastic.co/t/aggregating-fields-within-document/272773>\
**Category:** Elasticsearch\
**Created:** [May 12, 2021, 7:29am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773 "2021-05-12T07:29:39Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![agKaspar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/agkaspar/32/101578_2.png) [@agKaspar](https://discuss.elastic.co/u/agKaspar)\
**Post date:** [May 12, 2021, 7:29am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/1 "2021-05-12T07:29:39Z")

</div>

Hi, We have a mechanism which handles a queue of database operations (INSERT, DELETE and UPDATE operations) for various tables, I am now working on creating a Kibana dashboard with some metrics about this queue, but am hitting a wall.

I've currently got an application which gets the queue size every 10 minutes and indexes this to elastic search as this type of document:

```json
{
  "timeStamp": "2021-05-12T08:30:14.1324986+02:00",
  "tableA": {
    "insert": 2,
    "update": 3,
    "delete": 1
  },
  "tableB": {
    "insert": 0,
    "update": 2,
    "delete": 0
  }
}

```

I would like to start with the simplest dashboard item, which would be a total counter of all pending operations, for all tables, so with the above example, that would be `8`.  
From what I read, I think I would have to use a scripted metric, however I don't see a way in the docs to calculate all subfields by wildcard, is there any way to accomplish this?

Later on, I would also like a total metic for all operations per table. And finally, create a graph showing these numbers over time, based on the `timeStamp` field.

I would also be open to structuring this data in another way, it that would help with aggregating it.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [May 12, 2021, 7:53am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/2 "2021-05-12T07:53:06Z")

</div>

It might be easier to change the structure of your document a little and use a ingest pipeline like this

```auto
PUT test/_doc/1
{
  "timestamp": "2021-05-12T08:30:14.1324986+02:00",
  "tables": [
    {
      "name": "tableA",
      "insert": 2,
      "update": 3,
      "delete": 1
    },
    {
      "name": "tableB",
      "insert": 0,
      "update": 2,
      "delete": 0
    }
  ]
}

POST _ingest/pipeline/_simulate
{
  "docs": [
    {
      "_source": {
        "timestamp": "2021-05-12T08:30:14.1324986+02:00",
        "tables": [
          {
            "name": "tableA",
            "insert": 2,
            "update": 3,
            "delete": 1
          },
          {
            "name": "tableB",
            "insert": 0,
            "update": 2,
            "delete": 0
          }
        ]
      }
    }
  ],
  "pipeline": {
    "processors": [
      {
        "script": {
          "source": """
          ctx.total = 0;
          ctx.total += ctx.tables.stream().mapToInt(v -> v.insert+ v.update + v.delete).sum();

          """
        }
      }
    ]
  }
}

```

hope that helps as a start

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [May 12, 2021, 7:53am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/3 "2021-05-12T07:53:06Z")

</div>

Welcome!

I'd probably create documents like this:

```auto
POST /tables/_doc
{
  "@timestamp": "2021-05-12T08:30:14.1324986+02:00",
  "name": "tableA",
  "current": 6,
  "insert": 2,
  "update": 3,
  "delete": 1
}
POST /tables/_doc
{
  "@timestamp": "2021-05-12T08:30:14.1324986+02:00",
  "name": "tableB",
  "current": 2,
  "update": 2
}

```

Then I'd run a `sum` agg on `current`.

Also note that I changed `timeStamp` to `@timestamp` which kind of a standard field name.

---

<div class="post-metadata">

**Author:** ![agKaspar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/agkaspar/32/101578_2.png) [@agKaspar](https://discuss.elastic.co/u/agKaspar)\
**Post date:** [May 12, 2021, 9:09am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/4 "2021-05-12T09:09:41Z")

</div>

Thanks for both your suggestions, I went with @dadoonet's suggestion, which does work fine for creating some charts and counters.  
However, If I aggregate using sum, and enlarge my timespan (I'm indexing all pending operations, for all tables, every 10 minutes) to e.g. 24h, the aggregate function will start summing up different 'snapshots' , causing the numbers to appear twice as large.  
Is there way to tell Kibana to only group by specific timestamp, or I could e.g. add a guid to each record, which would be the same for all records belonging to 1 snapshot, and then group by that guid.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [May 12, 2021, 9:13am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/5 "2021-05-12T09:13:52Z")

</div>

I'm not sure I understood what the problem is but you can first aggregate with a `date_histogram` and then do a `sum`. It will sum per time period.

---

<div class="post-metadata">

**Author:** ![agKaspar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/agkaspar/32/101578_2.png) [@agKaspar](https://discuss.elastic.co/u/agKaspar)\
**Post date:** [May 12, 2021, 9:48am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/6 "2021-05-12T09:48:03Z")

</div>

So I'm trying to get a graph over time in kibana, using the lens visualisation. This works, but when I set the time period too big (e.g. 24h), the graphs groups together the values per hour or so, causing totals of 2 'snapshots' to be summed up together, making the graph appear as if there were double the amount of operations pending.

This is the data shown over the last 2 hours, which is fine (although I'd like to get rid of the 0 values in between):

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/c/0cfcd139bed6217f977f4a52d6bc635b752f934e.jpeg)

But this is the same data over the last 24h:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/4/9452767d5f4d70e181caf7447f455c389d0e9295.png)

In the latter graph, it summed up to 'snapshots' of data, showing a total of 90k instead of 2x 45k

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [May 12, 2021, 10:00am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/7 "2021-05-12T10:00:08Z")

</div>

So you want to aggregate by `name` first using a `terms` agg?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 9, 2021, 10:00am UTC](https://discuss.elastic.co/t/aggregating-fields-within-document/272773/8 "2021-06-09T10:00:27Z")

</div>

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