Querying a subfield in DocumentDB to sort and get the latest date

Question

To add/expound on my recent question

Below are DocumentDB collections: "delivery"

{
    "doc": [
        {
            "docid": "15",
            "deliverynum": "123",
            "text": "txxxxxx",
            "date": "2019-07-18T12:37:58Z"
        },
        {
            "docid": "17",
            "deliverynum": "999",
            "text": "txxxxxx",
            "date": "2018-07-18T12:37:58Z"
        }
    ],
    "id": "123",
    "cancelled": false
},
{
    "doc": [
        {
            "docid": "16",
            "deliverynum": "222",
            "text": "txxxxxx",
            "date": "2019-07-18T12:37:58Z"
        },
        {
            "docid": "17",
            "deliverynum": "999",
            "text": "txxxxxx",
            "date": "2019-07-20T12:37:58Z"
        }
    ],
    "id": "124",
    "cancelled": false
}

I need to search the deliverynum=999 w/ the latest date to get the "id", which in the case above is "124" because it has the latest "date" in the "doc" w/ deliverynum=999.

I was going to do:

var list = await collection.Find(filter).Project(projection).ToListAsync();

then do a LINQ to sort, but the problem here is my projection change the list from my Model Class to BsonDocument even if my projection included all the fields.

Was looking for a way to either get just needed "id" or get the single document.

Dĵ ΝιΓΞΗΛψΚ · Accepted Answer · 2019-07-25T14:41:25.653

1

i believe the following will do the trick. (if i understood your requirement correctly)

var result = collection.Find(x => x.docs.Any(d => d.deliverynum == 999))
                       .Sort(Builders<Record>.Sort.Descending("docs.date"))
                       .Limit(1)
                       .Project(x=>x.Id) //remove this to get back the whole record
                       .ToList();

update: strongly typed solution

var result = collection.AsQueryable()
                       .Where(r => r.docs.Any(d => d.deliverynum == 999))
                       .SelectMany(r => r.docs, (r, d) => new { r.Id, d.date })
                       .OrderByDescending(x => x.date)
                       .Take(1)
                       .Select(x => x.Id)
                       .ToArray();

edited Jul 25 '19 at 14:41

answered Jul 25 '19 at 13:10

Dĵ ΝιΓΞΗΛψΚ

5,068
3
13
26

Anyway I could do this without putting the string "docs.date" ? because we use a Class for our functions. It would be reusable for other calls as well. – Sid Jul 25 '19 at 13:28
nevermind, i think its good enough just to pass it as string. Thank you. – Sid Jul 25 '19 at 13:44
1

@sid added a strongly-typed solution. – Dĵ ΝιΓΞΗΛψΚ Jul 25 '19 at 14:42

Querying a subfield in DocumentDB to sort and get the latest date

1 Answers1