Search code examples
couchbasenickel

How to obtain documents which don't contain object with specific value inside an array

For example, I have two documents:

{
  communication: "some data 1"
  users: [
    {
      name: 'Peter',
      role: 'admin'
      gender: 'male'
    },
    {
      name: 'John',
      role: 'guest'
      gender: 'male'
    }
  ]
}


{
  communication: "some data 2"
  users: [
    {
      name: 'Kollins',
      role: 'admin'
      gender: 'male'
    },
    {
      name: 'Steve',
      role: 'moderator'
      gender: 'male'
    }
  ]
}

Finally, I have to obtain documents that don't have inside a users array the user with role: 'guest'. Basing on the example of two documents I have to obtain the second one. Pseudo code is like: select * from bucket where users.role = 'guest' is missing

Solution

  • You need to map the users array with ARRAY and check if there is a user with NOT EXISTS

    SELECT * FROM bucket WHERE NOT EXISTS ARRAY users FOR user IN users 
WHEN user.role='guest' END