CODEWITHSUNDEEP
mongodb
JayK
JayK

Reputation: 181

Mongodb How to match one of two field do not equal zero?

I need to match one of two fields that must not be equal to zero. How to implement it?

I try these solutions but no luck:

Solution 1:

Model.aggregate[
{
            $project: {
                accountID: "$_id.accountID",
                locationID: "$_id.locationID",
                time: "$_id.time",
                value: "$value",
                actualValue: "$actualValue",
                total: { $add: ["$value", "$actualValue"] },
            },
        },
        {
            $match: {
                total: { $ne: 0 },
            },
        },
]

With this solution, it will wrong when a negative plus with the opposite version. Example -1500 + 1500 will become zero.

Solution 2

Model.aggregate([
        {
            $group: {
                _id: {
                    accountID: "$accountID",
                    locationID: "$locationID",
                    time: "$time",
                },
                value: { $sum: "$values.val" },
                actualValue: { $sum: "$values.actualVal" },
            },
        },
        {
            $addFields: {
                absVal: { $abs: "$value" },
                absActualVal: { $abs: "$actualValue" },
            },
        },
        {
            $project: {
                accountID: "$_id.accountID",
                locationID: "$_id.locationID",
                time: "$_id.time",
                value: "$value",
                actualValue: "$actualValue",
                total: { $add: ["$absVal", "$absActualVal"] },
            },
        },
        {
            $match: {
                total: { $ne: 0 },
            },
        },
])

It works, but I lost 1 second from 3.5s to 4.5s when searching in 1m document.

Any suggestion? Thank you first

Upvotes: 1

Views: 787

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

Some basic boolean logic should suffice, use something like:

Model.aggregate([
    {
        $match: {
            $or: [
                {
                    value: {$ne: 0}
                },
                {
                    actualValue: {$ne: 0}
                }
            ]
        }
    }
    {
        $project: {
            accountID: "$_id.accountID",
            locationID: "$_id.locationID",
            time: "$_id.time",
            value: "$value",
            actualValue: "$actualValue",
            total: {$add: ["$value", "$actualValue"]},
        },
    }
])

If you care about efficiency make sure you have a compound index that covers both value and actualValue.

Upvotes: 1

Related Questions