0

Given a datastore entity structure where one column (dates) contains an object array: 

[{date: 2018-01-01T00:00:00Z, otherProps:x }, {date: 2018-01-02T00:00:00Z, otherProps: x}, {date: 2017-12-31T00:00:00Z, otherProps: x}]

Is it possible to construct a complex index and a query on the earliest date within the entities dates array.

For example, I would like to select all entities where the earliest date is before a certain date... This is my first foray into NoSQL so apologies if this is obvious!

Robin Southgate
  • 487
  • 1
  • 5
  • 11

2 Answers2

1

That may be tricky because query ordering and filtering are, in general, based on the property values and in your case the values would be the entire date arrays, not just the date values inside them.

Depending on the actual client library you use it might be possible. See, for example, Structured Properties and Filtering for Structured Property Values available with the ndb client library.

Personally I'd rather create separate entities for each of these date arrays, with the date and otherProps as properties - much simpler to use as sort/filter in queries. And in general a more scalable approach, IMHO, since it avoids the lists of (repeated) date array properties, see Creating your own activity logging in GAE/P.

Dan Cornilescu
  • 39,470
  • 12
  • 57
  • 97
  • I'm using Node - the google dartastore js library (https://www.npmjs.com/package/@google-cloud/datastore). These are archive records from firebase where the mutliple dates as keys on an object makes much more sense, so while I could alter the structure as the record gets archived I was hoping not to just for ease of parity between active and archive records... Following your link to Structured Properties I see [Computed Properties](https://cloud.google.com/appengine/docs/standard/python/ndb/entity-property-reference#structured) but is that only available in python library? – Robin Southgate Apr 08 '18 at 08:25
  • It appears that the [Embedded entity](https://cloud.google.com/datastore/docs/concepts/entities#embedded_entity) *might* be what you're looking for: `Notes: When indexed, you can query on subproperties`. But I don't know how exactly are those used. – Dan Cornilescu Apr 09 '18 at 02:34
0

You can reference to a element in a query by simple using arrayName.date < certainDate. It will work because the index is not composite, it means you are not trying to filter something like yourKind.property = value and arrayName.date < certainDate.

I strongly recommend you not to save date as string but instead using a timestamp (like javascript (new Date()).getTime()), it will make it very much easier in the long therm, mainly if you are using more than one timezone.

JLCDev
  • 609
  • 1
  • 5
  • 18
  • The datastore schema is not mine and I don't currently have the option of changing the date to a timestamp, though they are ISO 8601 which I *think* was designed so that string comparators like < would work... though not sure if that is true with the timezone bit. – Robin Southgate Apr 08 '18 at 08:22