Query to get sum of a field values plus 100

MongoDB Atlas
1

Hi guys, I am new to mongo and trying to right query with a sum operator but with no luck.
I need to check this logic:

  • If fullReviewScore > zip_score_threshold

    • RecommendationScoreV3 = fullReviewScore + 100

  • Else

    • RecommendationScoreV3 = fullReviewScore + 0

  • Sort the listing in Group 1

    • RecommendationScoreV3 (Desc ⇒ Asc) [First level sort]
    • Distance (Asc ⇒ Desc) [Second level sort]

What I did:

$match
{
  "communities.fullReviewScore":{$gt:8}
}

$project
{
  "communities.recommendedScoreV3":{
    $eq:{"communities.recommendedScoreV3",
    $sum:{"communities.fullReviewScore",100 }
      
    }
  }
}

$count
{'results'}

could you help me please!

2

Hi @Natalia_Merkulova, it would be nice if you provided along with your description of what you’re trying to do, some sample documents (remove any unnecessary fields to keep the size small) and the results/errors after running the command. This makes it easier for other members to help you determine what’s going on, and makes it more likely that you get a reply.

It’s also easier to read if you put your sample code in a code block. You can do that by going to a blank line and then clicking on the Preformatted text icon ( </>). This makes it easier to read the code, and more importantly copy/paste the code (regular text on the forum uses fancy quotes which which cause problems if one were to copy/paste the text).

I can say that the code as you have it above will cause errors as it’s not properly written Mongo query/JSON. I don’t know if that’s what you’re having problems with and need help with or if it’s something else.

3

Hi @Doug_Duncan , this is an example of doc:

{
  "_id": {
    "$oid": "627ca4981fc9c169c6a0ea8e"
  },
  "careType": "ALZHEIMERS_CARE",
  "relativeUrl": "florida/miami",
  "communities": [
    {
      "_id": {
      "distance": 9.273651757596374,
      "id": 1436709,
      "imageCount": 21,
      "isCustomer": true,
      "name": "Mirabelle",
      "orgName": "The Arbor Company",
      "priceAverage": 7422.857142857143,
      "ratingAverage": 4.8,
      "ratingScore": 65000,
      "reviewCount": 6,
      "reviewScore": {
        "reviewScoreDisplay": 9.4,
        "reviewScoreFull": 9.433,
   
      },
    "recommendedScoreV3": 109.43299999999999,
    "fullReviewScore": 9.433
}]
4

@Doug_Duncan
I am trying to check:
1.

  • If fullReviewScore > 8
    • RecommendationScoreV3 = fullReviewScore + 100
  • If fullReviewScore <= 8
    • RecommendationScoreV3 = fullReviewScore + 0
  • Sort the listing in Group 1
    • RecommendationScoreV3 (Desc ⇒ Asc) [First level sort]
    • Distance (Asc ⇒ Desc) [Second level sort]
5

Thanks for supplying the sample document.

Looking at the given document, we can see that the field that will be used for determining if we add 100 or not is nested in a sub-document in an array. The distance is also in this same array sub-document. Based on this you will want to use $unwind to break each array element into its own document. This allows for multiple communities to be dealt with properly. After that you add a field that does the addition logic based on the fullReviewScore field and then do your sort.

The following should do what you’re looking for:

db.test.aggregate(
    [
        {
            "$unwind": { "path": "$communities" }
        },
        {
            "$addFields": {
                "recommendationScorev3": {
                    "$cond": {
                        "if": { "$gt": ["$communities._id.fullReviewScore", 8] },
                        "then": { "$add": ["$communities._id.fullReviewScore", 100] },
                        "else": "$communities._id.fullReviewScore"
                    }
                }
            }
        },
        {
            "$sort": {"recommendationScoreV3": -1, "distance": 1}
        }
    ]
)

In the above you can see that is used $addFields instead of $project like you had in your original attempt. I did this since I wanted to keep the entire original document. Had I used $project I would have had to put all the top level fields in for projection.

Also notice that your $match stage criteria has been moved into the $addFields stage as a $cond condentional operator. If I would have used the $match stage, then you wouldn’t have gotten the complete list of documents in your resulting output.

Hopefully this helps you with what you’re trying to do.

3 Likes
6

@Doug_Duncan Thank you so much!!! You helped a lot and thank you for all your notes!!! I am really veery grateful!!!

Have an amazing day!

2 Likes