I have a simple Datastore kind having the following properties:
- id (long)
- createdAt (timestamp)
- userId (string)
- metrics (array of complex objects)
- type of metric
- value of metric
Each stored row in the Datastore might have a different amount of metrics as well as different types of metrics.
I have a very specific requirement to query the latest metrics of a user. The problem here is that different rows have different metrics so I can't just take the most recent row, I need to look into metrics array to retrieve all the data.
I decided to use projection queries. My idea was to create a projection based on the following properties: metrics.type, metrics.value and use distinct on metrics.type and adding order by createdAt desc.
For a better explanation, a simple example of rows from the Datastore:
1. { "id": 111, "createdAt": "2019-01-01 00:00", "userId" : "user-123", [{ "type" : "metric1", "value" : 123 }, { "type" : "metric2", "value" : 345 }] }
2. { "id": 222, "createdAt": "2019-01-02 00:00", "userId" : "user-123", [{ "type" : "metric3", "value" : 567 }, { "type" : "metric4", "value" : 789 }] }
I expected a projection query with distinct on metrics.type filter to return the following results:
1. "metric1", 123
2. "metric2", 345
3. "metric3", 567
4. "metric4", 789
but actually what query returns is:
1. "metric1", 123
2. "metric2", 123
3. "metric3", 123
4. "metric4", 123
So all metrics have the same value (which is incorrect). Basically it happens because of an exploded index - Datastore thinks I have 2 arrays but indeed it's a single array
Is there any way to make projection query to return what I expect instead of exploding the index? If not, how can I rebuild what I have so it meets my requirements?
