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.
