CODEWITHSUNDEEP
couchbasesql++
Rishabh Jain
Rishabh Jain

Reputation: 123

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

I have a document having nested structure like given below

{

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

How we can get project having "owner": "alertNotification"

Upvotes: 1

Views: 2081

Answers (1)

Matthew Groves
Matthew Groves

Reputation: 26096

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"
  }
]

Upvotes: 4

Related Questions