Custom return MongoDB aggregate-CodePudding

I'm trying to do some testing with MongoDB and I have figured some of the simpler MySQL queries MongoDB. Now, I have this slightly more complex query.

I have this query that tells me if there was a message in a certain period from a determined user:

SELECT CASE WHEN count(1) = 0 THEN false ELSE true END AS 'value'
FROM messages m
WHERE m.time BETWEEN 0 AND 1652471890 AND m.user_id = '256f5280-fb49-4ad6-b7f5-65c4329d46e0';

Currently I am trying to do this to count the amount and emit a custom value 0/1:

Current MongoDB Aggregate

    db.messages.aggregate([
        { $match: 
            {
            $and: [
              {user_id: "256f5280-fb49-4ad6-b7f5-65c4329d46e0"},
              {time: {$gt: 1622471890, $lt: 1822471890}}
            ]
            } 
        },
        { $count: "value"}
    ])

Dataset:

    [
    {
        "time": 1422471890,
        "user_id": "256f5280-fb49-4ad6-b7f5-65c4329d46e0",
        "message": "This is an example of my db"
    },
    {
        "time": 1622471890,
        "user_id": "256f5280-fb49-4ad6-b7f5-65c4329d46e0",
        "message": "This is an example of my db (1)"
    },
    {
        "time": 1622471890,
        "user_id": "256f5280-fb49-4ad6-b7f5-65c4329d46e0",
        "message": "This is an example of my db (2)"
    },
    {
        "time": 1622471890,
        "user_id": "e194d667-d79f-4262-94b1-ecf4561c9418",
        "message": "This is an example of my db (3)"
    },
    {
        "time": 1922471890,
        "user_id": "256f5280-fb49-4ad6-b7f5-65c4329d46e0"<
        "message": "This is an example of my db (4)"
    }
    ]

Return:

With this dataset it's returning:

{ "value" : 2 }

I'm trying make its return:

If count > 0: { "value": 1 }

If count <= 0: { "value": 0 }

CodePudding user response：

You just need one more $addFields stage to apply $cond to your value

db.collection.aggregate([
  {
    $match: {
      $and: [
        {
          user_id: "256f5280-fb49-4ad6-b7f5-65c4329d46e0"
        },
        {
          time: {
            $gte: 1622471890,
            $lt: 1822471890
          }
        }
      ]
    }
  },
  {
    "$count": "value"
  },
  {
    "$addFields": {
      "value": {
        "$cond": {
          "if": {
            "$gt": [
              "$value",
              0
            ]
          },
          "then": "$value",
          "else": 0
        }
      }
    }
  },
  {
    "$unionWith": {
      "coll": "collection",
      "pipeline": [
        {
          $limit: 1
        }
      ]
    }
  },
  {
    "$sort": {
      value: -1
    }
  },
  {
    $limit: 1
  },
  {
    "$project": {
      _id: 0,
      value: {
        "$ifNull": [
          "$value",
          0
        ]
      }
    }
  }
])

Here is the Mongo Playground for your reference.