How to search in array with in an array in couchbase query

I'm not exactly sure what you have in mind (you may want to check out this SO question for details about UNNEST vs ANY/SATISFIES), but if you want to just select the nested project objects where owner == 'alertNotification', you can use UNNEST. You've got an array within an array, so you'd need to UNNEST twice:

SELECT prj.*
FROM moviegame b
UNNEST b.accounts acct
UNNEST acct.projects prj
WHERE prj.owner == 'alertNotification';

That would return:

[
  {
    "email": "[email protected]",
    "id": "2",
    "name": "abcdCompany alert notification",
    "owner": "alertNotification"
  }
]

If you DON'T want to UNNEST, and you just want to return any document which has an account which has a project which has an 'owner' of 'alertNotification', then you can use ANY/SATISFIES (again nested, because there's an array within an array):

SELECT b.*
FROM moviegame b
WHERE ANY a IN accounts SATISFIES (ANY p IN a.projects SATISFIES p.owner == 'alertNotification' END) END

which would return the whole document in your sample (below) but would NOT include other documents that do NOT have a project with owner=='alertNotification' within them:

[
  {
    "accounts": [
      {
        "email": "[email protected]",
        "id": "123123",
        "name": "abcdCompany",
        "owner": "abcdCompany corporation",
        "projects": [
          {
            "email": "[email protected]",
            "id": "1",
            "name": "abcdCompany asset management",
            "owner": "assetMgmt"
          },
          {
            "email": "[email protected]",
            "id": "2",
            "name": "abcdCompany alert notification",
            "owner": "alertNotification"
          }
        ]
      }
    ],
    "docType": "account"
  }
]