MongoDB Boolean Query-CodePudding

I have a story collection of documents as below

[
    {
        createdBy:'user1',
        storyId:1,
        viewers:['user3'],
    },
     {
        createdBy:'user2',
        storyId:2,
        viewers:['user3'],
    },

    {
        createdBy:'user2',
        storyId:3,
        viewers:['user4'],
    },

    {
        createdBy:'user5',
        storyId:4,
        viewers:['user3'],
    },

    {
        createdBy:'user5',
        storyId:5,
        viewers:['user3'],
    }
]

Suppose user3 requests to get the status of each story of a specific user viewed by him or not. I want to return TRUE if the viewers array contains the user id of him('user3') in all documents and FALSE if any document not contains his userid in array. Hope you understand my question.

Expected Output

[
    {
        createdBy:'user1',
        isAllViewed: true
    },

    {
        createdBy:'user2',
        isAllViewed: false
    },

    {
        createdBy:'user5',
        isAllViewed: true
    },  
]

CodePudding user response：

You can try this aggregation query:

Using the same idea as before, use $setInteresection to know if user3 is in the array. It produces an array with values true or false if exists or no.

So next stage is check $allElementsTrue.

db.collection.aggregate([
  {
    "$group": {
      "_id": "$createdBy",
      "isAllViewed": {
        "$push": {
          "$ne": [
            {
              "$setIntersection": [
                "$viewers",
                [
                  "user3"
                ]
              ]
            },
            []
          ]
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "createdBy": "$_id",
      "isAllViewed": {
        "$allElementsTrue": "$isAllViewed"
      }
    }
  }
])

Example here

CodePudding user response：

Query

group by createdBy
create an array of [true false ...] based on if user3 is contained in "viewers"
and then checks if in that array all are true

Test code here

db.collection.aggregate([
  {
    "$group": {
      "_id": "$createdBy",
      "isAllViewed": {
        "$push": {
          "$in": [
            "user3",
            "$viewers"
          ]
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "createdBy": "$_id",
      "isAllViewed": {
        "$allElementsTrue": "$isAllViewed"
      }
    }
  }
])