Querying a subfield in documentdb

Question

For example I have a document below for collection = delivery:

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

is it possible to do a search with "deliverynum" = 999 and the output would be like below?

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

or should I make another Collection just for the Doc part?

I am having trouble making a query in C# for this kind of scenario.

Are you expecting the `doc` to be an array or just a plain document? — Rakshith Murukannappa, Jul 18 '19 at 15:24
@RakshithSm it is an array object yes. 1 delivery id = many docid — Sid, Jul 19 '19 at 04:04

score 4 · Accepted Answer · answered Jul 18 '19 at 15:47

4

In Mongo shell you can use the $(projection) operator:

db.collection.find({ "doc.deliverynum": "999" }, { "doc.$": 1 })

Corresponding C# code can look like below:

var q = Builders<Model>.Filter.ElemMatch(x => x.doc, d => d.deliverynum == "999");
var p = Builders<Model>.Projection.ElemMatch(x => x.doc, d => d.deliverynum == "999");

var data = Col.Find(q).Project(p).ToList();

You can also use q = Builders<Model>.Filter.Empty if you want to get all documents even if the don't contain deliverynum =``999

answered Jul 18 '19 at 15:47

mickl

48,568
9
60
89

Hi @mickl, thank you. If I may add another question, how do I add a Sort for the delivernum = 999 and get the one with the latest date. Given that my doc can be more than one with delivery = 999? Also, if it is possible to still retain my Model Type because using projection would convert it to Bson. – Sid Jul 25 '19 at 11:31
@Sid that's possible but the solution will be totally different. Could you open a separate question and link it here ? I will try to take a look at it later today – mickl Jul 25 '19 at 11:32
Hey, using this answer, how can I project to ModelChild instead of just Model? I want to count how many ModelChilld there are in the database with criteria. I opened a question for that too : https://stackoverflow.com/questions/58572968/how-to-count-select-and-update-nested-elements-in-mongodb-c-sharp#comment103462808_58572968 – programad Oct 26 '19 at 18:25
You could use `$replaceRoot` but to make it simpler just run `Col.Find(q).Project(p).ToList().Select(d => d.doc)` since you know that there will be just this subdocument returned from the database – mickl Oct 26 '19 at 18:31
Ok, I go this: DbSet.AsQueryable().SelectMany(x => x.Comments).Where(where).Count(); but will this run on memory or on DB? – programad Oct 26 '19 at 19:58

Querying a subfield in documentdb

1 Answers1

Linked