How to calculate time difference across multi-layer nested structure

Elastic Stack Elasticsearch
1

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 :smiley:

2

I highly encourage you to do this at index time using a script processor - 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!

3

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 :slight_smile:

1 Like
4

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