# How to calculate time difference across multi-layer nested structure

**URL:** <https://discuss.elastic.co/t/how-to-calculate-time-difference-across-multi-layer-nested-structure/248794>\
**Category:** Elasticsearch\
**Created:** [September 16, 2020, 8:51am UTC](https://discuss.elastic.co/t/how-to-calculate-time-difference-across-multi-layer-nested-structure/248794 "2020-09-16T08:51:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Fan\_Y102](https://avatars.discourse-cdn.com/v4/letter/f/f6c823/32.png) [@Fan\_Y102](https://discuss.elastic.co/u/Fan_Y102)\
**Post date:** [September 16, 2020, 8:51am UTC](https://discuss.elastic.co/t/how-to-calculate-time-difference-across-multi-layer-nested-structure/248794/1 "2020-09-16T08:51:07Z")

</div>

Hello Guys,  
I'm new for Elasticsearch query. Currently, I'm working on a data set contains two layers nested JSON. For example:

```
{
"data": [
	{
		"number": 1,
		"startDate": "2020-01-08",
		"finalDate": "2020-01-04",
		"items": [
			{
				"Id": 8154725563,
				"Code": "1",
				"finalDate": "2020-01-04"
			},
			{
				"Id": 8154725564,
				"Code": "2",
				"finalDate": "2020-01-04"
			}
		]
	},
	{
		"number": 2,
		"startDate": "2020-02-08",
		"finalDate": "2020-02-06",
		"items": [
			{
				"Id": 8154725568,
				"Code": "1",
				"finalDate": "2020-02-06"
			},
			{
				"Id": 8154725569,
				"Code": "2",
				"finalDate": "2020-02-06"
			},
			{
				"Id": 8154725572,
				"Code": "71",
				"finalDate": "2020-02-06"
			}
		]
	}
] }

```

I would like to find the solution if I can calculation time difference between first layer and second layer. Like data.startDate - data.items.finalDate  
When I running my query, it looks like I cannot get result across different nested layer.

> {  
> "size":0,  
> "query":{  
> "match":{  
> "userid":1234576  
> }  
> },  
> "aggs": {  
> "all\_data": {  
> "nested": {  
> "path": "data"  
> },  
> "aggs": {  
> "data\_terms": {  
> "nested": {  
> "path": "data.items"  
> },  
> "aggs": {  
> "cnt\_items\_limit": {  
> "filter": {  
> "bool": {  
> "must": [  
> {  
> "script": {  
> "script": "if (doc['data.items.finalDate'].size()==0) {return new Date().getTime() - doc['data.startDate'].value.toInstant().toEpochMilli() \<= 0} else {return doc['data.items.finalDate'].value.toInstant().toEpochMilli() - doc['data.startDate'].value.toInstant().toEpochMilli() \<= 0}"  
> }  
> }  
> ]  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }  
> }

Can you help on it? Thank you 😃

---

<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:** [September 17, 2020, 12:43pm UTC](https://discuss.elastic.co/t/how-to-calculate-time-difference-across-multi-layer-nested-structure/248794/2 "2020-09-17T12:43:01Z")

</div>

I highly encourage you to do this at index time using a [script processor](https://www.elastic.co/guide/en/elasticsearch/reference/7.9/script-processor.html) - you will still have to write the logic to find the corresponding start/end dates yourself though.

The trick with accessing the elements is to also specify the index of the `items` array which you did not do. Hope that helps!

---

<div class="post-metadata">

**Author:** ![Fan\_Y102](https://avatars.discourse-cdn.com/v4/letter/f/f6c823/32.png) [@Fan\_Y102](https://discuss.elastic.co/u/Fan_Y102)\
**Post date:** [September 21, 2020, 1:51am UTC](https://discuss.elastic.co/t/how-to-calculate-time-difference-across-multi-layer-nested-structure/248794/3 "2020-09-21T01:51:07Z")

</div>

Hello @spinscalem，  
Thank you for your suggestion, I find my search query only need items.Code=1, so I just reverse the nested JSON to upper level when data ingest to ES and it might easy for me to implement it 🙂

---

<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:** [October 19, 2020, 1:51am UTC](https://discuss.elastic.co/t/how-to-calculate-time-difference-across-multi-layer-nested-structure/248794/4 "2020-10-19T01:51:07Z")

</div>

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