Mongodb query sub document $gt

Question

I had searched other posts, but what seems to work fine just couldn't work here. I need your advice.

Here is what my document looks like in the database, just one document with a series of tag in it.

I need to just query, the restaurant type which has counter greater than 0, (so the end result will exclude any type with counter 0)

My schema

const tagsSchema = mongoose.Schema({
  _id: mongoose.Schema.Types.ObjectId,
  details: {
    restaurantTypeId: mongoose.Schema.Types.ObjectId,
    restaurantTypes: [{
      _id: mongoose.Schema.Types.ObjectId,
      name: String,
      counter: Number,
    }],
    foodTypeId: mongoose.Schema.Types.ObjectId,
    foodTypes: [{
      _id: mongoose.Schema.Types.ObjectId,
      name: String,
      counter: Number,
    }]
  }
});

I have tried

    tags.find({
    'details.restaurantTypes.counter': {
        $gt: 0
    }
}, (err, data) => {
    if (err) {
        res.send(err);
    }
    res.json(data);
});

and I got

[
{
    "details": {
        "restaurantTypeId": "5c01fb57497a896d50f49877",
        "restaurantTypes": [
            {
                "_id": "5c01fb57497a896d50f49879",
                "name": "Asian",
                "counter": 1
            },
            {
                "_id": "5c01fb57497a896d50f4987a",
                "name": "Bakery",
                "counter": 0
            },
            {
                "_id": "5c01fb57497a896d50f4987b",
                "name": "Barbecue",
                "counter": 0
            },
            {
                "_id": "5c01fb57497a896d50f4987c",
                "name": "Bars & Pubs",
                "counter": 0
            },
            {
                "_id": "5c01fb57497a896d50f4987d",
                "name": "Bistro",
                "counter": 0
            },

and

    tags.find({
    'details.restaurantTypes.counter': {
        $gte: 1
    }
}, (err, data) => {
    if (err) {
        res.send(err);
    }
    res.json(data);
});

which give me the same result

score 1 · Answer 1 · answered Dec 25 '18 at 01:32

1

You can use aggregation pipeline to filter the restaurantTypes

$match - filter the restaurant
$addFields - to overwrite restaurantTypes and $filter the restaurant types by counter

aggregated pipeline

 db.res.aggregate([
        {$match: {"_id" : ObjectId("5c2187be640edfe094a3b946")}},
        {$addFields:{"restaurantTypes" : {$filter : {input : "$restaurantTypes", as : "t", cond : {$ne : ["$$t.counter",0]}}}}}
    ])

answered Dec 25 '18 at 01:32

Saravana

12,647
2
39
57

Hi, thanks for replying, I be sure to try that out later, did you find any error in my 'find $gt' method? because that seems like more simpler and straightforward in concept, which I found in mongodb documentations – Thomas Kim Dec 25 '18 at 01:46
1

You can't do with find, the whole document will be returned if there one match is found in the array so you'll have to use aggregation pipeline with $filter to limit the projection – Saravana Dec 25 '18 at 01:50
Oh, so the $gt is only to filter among many documents, and not used in one document right? – Thomas Kim Dec 25 '18 at 01:52
Hi Saravana, I just tried your approach, but I still got the same result. I will check out the filtering in one document MongoDB. – Thomas Kim Dec 25 '18 at 02:46

score 0 · Accepted Answer · answered Dec 25 '18 at 02:58

Okay, I found the answer, inspired by Saravana.

here is the answer using aggregate and filter.

    tags.aggregate([{
        $match: {
            "_id": mongoose.Types.ObjectId(id)
        }
    },
    {
        $project: {
            "details.restaurantTypes": {
                $filter: {
                    input: "$details.restaurantTypes",
                    as: "resType",
                    cond: {
                        $ne: ["$$resType.counter", 0]
                    }
                }
            }
        }
    }
]

this will give me the result

[
{
    "_id": "5c01fb57497a896d50f49876",
    "details": {
        "restaurantTypes": [
            {
                "_id": "5c01fb57497a896d50f49879",
                "name": "Asian",
                "counter": 1
            },
            {
                "_id": "5c01fb57497a896d50f498a6",
                "name": "Thai",
                "counter": 1
            },
            {
                "_id": "5c01fb57497a896d50f498a8",
                "name": "Western",
                "counter": 1
            }
        ]
    }
}

]

Mongodb query sub document $gt

2 Answers2