Upsi.
one DESC missing in this part of the query:
*let youngestN=(FOR item IN sortlist SORT item.youngest DESC LIMIT 1 RETURN
item._key)*
On Tuesday, November 7, 2017 at 5:26:16 PM UTC+1, Wilfried Gösgens wrote:
>
> Hi,
> Iterating to a solution using arangosh:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *# create the collection, save some test
> documentsdb._create('test')db.test.save({ _key:"1",
> history:[ {created: '2017-11-07', msg:
> 'abc'}, {created: '2016-11-07', msg: 'cde'}
> ] });db.test.save({ _key:'2',
> history:[ {created: '2016-11-06', msg:
> 'abc'}, {created: '2017-11-06', msg: 'cde'}
> ] })db.test.save({ _key:'3',
> history:[ {created: '2017-11-05', msg:
> 'abc'}, {created: '2016-11-05', msg: 'cde'}
> ] })*
>
>
> Now lets construct a query. Start with the inner most thing first.
>
> We iterate through the documents, and in them through the history object.
> We pick the youngest history object, and return the pair of the document
> `_key` combined with the date for later use:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *db._query(` let sortlist=(FOR doc IN test
> let youngest =( FOR historyItem IN
> doc.history SORT historyItem.created
> DESC LIMIT 1 RETURN
> historyItem.created ) return
> {_key: doc._key, youngest: youngest[0]}) return sortlist
> `)[ [ { "_key" : "1", "youngest" : "2017-11-07" },
> { "_key" : "2", "youngest" : "2017-11-06" }, {
> "_key" : "3", "youngest" : "2017-11-05" } ] ]*
>
>
> So now we see in our first step, that we got the document ids (`_key`),
> and the youngest date from the documents.
>
> Now we want to limit that to an array of the youngest IDs that we will be
> finally interested in: (we only pick the very youngest)
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *db._query(` let sortlist=(FOR doc IN test
> let youngest =( FOR historyItem IN
> doc.history SORT historyItem.created
> DESC LIMIT 1 RETURN
> historyItem.created ) return
> {_key: doc._key, youngest: youngest[0]}) let youngestN=(FOR item
> IN sortlist SORT item.youngest LIMIT 1 RETURN item._key) RETURN
> youngestN `)[ [ "3" ] ]*Now that we have a list of the
> youngest documents, we finaly fetch the documents to return them from the
> AQL:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *db._query(` let sortlist=(FOR doc IN test
> let youngest =( FOR historyItem IN
> doc.history SORT historyItem.created
> DESC LIMIT 1 RETURN
> historyItem.created ) return
> {_key: doc._key, youngest: youngest[0]}) let youngestN=(FOR item
> IN sortlist SORT item.youngest LIMIT 1 RETURN item._key) FOR doc IN
> test FILTER doc._key IN youngestN LIMIT 1 RETURN doc `)[ {
> "_key" : "3", "_id" : "test/3", "_rev" : "_V3ay2_2--_",
> "history" : [ { "created" : "2017-11-05", "msg" :
> "abc" }, { "created" : "2016-11-05", "msg" :
> "cde" } ] } ]*
> You now can play with the LIMITs depending on your needs.
>
> However, this approach has the disadvantage that it will not be able to
> utilize an array index.
>
> A better approach would probably be to keep some attributes like
> `youngest` and `oldest` next to `history` and at least be able to
> filter/sort/limit on them before doing all that fine grained cpu intense
> work I described above.
>
> Cheers,
> Willi
>
>
>
> On Tuesday, November 7, 2017 at 3:43:26 PM UTC+1, Andreas Jung wrote:
>>
>> Hi there,
>>
>> I have a datastructure like this
>>
>> [ { id=1,
>> history=[
>> {created: date1, msg:...},
>> {created: date2, msg: }
>> ]};
>> { id=2,
>> history=[
>> {created: .., msg:...},
>> {created: ..., msg: }
>> ]};
>> ...
>>
>> }
>>
>> The `history` array contains logging information for the overall record.
>>
>> Can I query such a collection using AQL returning the documents with the
>> N-newest `history.created` entries?
>>
>> -aj
>>
>
--
You received this message because you are subscribed to the Google Groups
"ArangoDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
