Retrieve a schedule using MapReduce or N1QL

Hi,

Let’s say I have such documents among another documents

{“type”: “event”, “resource”: 1, “timestamp”: “1”, value: {}}
{“type”: “event”, “resource”: 1, “timestamp”: “4”, value: {}}
{“type”: “event”, “resource”: 1, “timestamp”: “5”, value: {}}
{“type”: “event”, “resource”: 2, “timestamp”: “3”, value: {}}
{“type”: “event”, “resource”: 2, “timestamp”: “4”, value: {}}
{“type”: “event”, “resource”: 3, “timestamp”: “1”, value: {}}

I need to query events for each resource on time interval at once (with additional condition, if some resource doesn’t have events on the interval its last event should be present in the result).

For example if I want to get a schedule from 5 to 4 I expect result will be:

{“type”: “event”, “resource”: 1, “timestamp”: “4”, value: {}}
{“type”: “event”, “resource”: 1, “timestamp”: “5”, value: {}}
{“type”: “event”, “resource”: 2, “timestamp”: “4”, value: {}}
{“type”: “event”, “resource”: 3, “timestamp”: “1”, value: {}} //since resource 3 doesn’t have event on the time interval, but still is active

How can I do it?

I was trying to create a view but in this case I can only get events on specified time interval.

So in general I have to find all resource types and get a schedule for each of them with a complex condition.
Can I do it using N1QL?

Thanks in advance!

SELECT d FROM default d WHERE timestamp BETWEEN "4" AND "5" AND type = "event"
UNION ALL
SELECT MAX([d.timestamp,d])[1] AS d FROM default d WHERE type = "event" 
 AND (timestamp < "4" OR timestamp > "5") GROUP BY resource;
1 Like

@ishmulyan, Above query may include “resource”: 2, “timestamp”: “3” try this.

SELECT d FROM default d WHERE timestamp BETWEEN "4" AND "5" AND type = "event"
UNION ALL
SELECT MAX([d.timestamp,d])[1] AS d FROM default d WHERE type = "event" GROUP BY resource
HAVING COUNT(CASE WHEN (timestamp BETWEEN "4" AND "5") THEN 1 ELSE NULL END) = 0;
1 Like

@vsr1, thank you for your response.

Should
COUNT(CASE WHEN (timestamp BETWEEN "4" AND "5") THEN 1 ELSE 0 END) = 0;
be
COUNT(CASE WHEN (timestamp BETWEEN "4" AND "5") THEN 1 ELSE null END) = 0;
or even shorter
COUNT(CASE WHEN (timestamp BETWEEN "4" AND "5") THEN 1 END) = 0;?

You are right.
COUNT(CASE WHEN (timestamp BETWEEN "4" AND "5") THEN 1 ELSE NULL END) = 0;
OR
SUM(CASE WHEN (timestamp BETWEEN "4" AND "5") THEN 1 ELSE 0 END) = 0;