I have a document that looks like this
{
“startTime”: 123,
“endTime”: 932345235,
“type”: “results”,
“validationResults”: [
{
“name”: “xyz”,
“ruleViolated”: 1,
“details”: [
{
“timeStamp”: 132345235,
“place”: “abc”,
“acknowledged”: false
},
{
“timeStamp”: 7658,
“place”: “bcd”,
“acknowledged”: true
}
]
},
{
"name": "xyz",
"ruleViolated": 2,
"details": [
{
"timeStamp": 132345235,
"place": "abc",
"acknowledged": false
},
{
"timeStamp": 7658,
"place": "bcd",
"acknowledged": true
}
]
},
{
"name": "random",
"ruleViolated": 1,
"details": [
{
"timeStamp": 9679,
"place": "abc",
"acknowledged": false
},
{
"timeStamp": 12412,
"place": "bcd",
"acknowledged": true
}
]
}
]
}
Now I want to have a query that counts unique name
within a given time range that may or maynot cover the entire document time range. So I have used the following query
SELECT count(distinct(b.name)) as count
FROM resultBucket UNNEST a.validationResults as b where
( (a.startTime <= 1459470600000 AND a.endTime >= 1459470600000) OR (a.startTime >= 1459470600000 AND a.startTime <= 1459503600276) )
AND ( any tag in b.results satisfies tag.timeStamp BETWEEN 1459470600000 and 1459503600276 end )
AND a.type = “results”
But the query is taking too long than expected. Around 2 mins.