how to get list of documents in couchbase where we have to filter base on attribute an array which is in another array?

Question

 "contact": {
  "extendedData": [],
  "id": "org_2_1084",
  "organizationId": 1084,
  "organizationName": "abc",
  "organizationRoles": [
    {
      "name": "NLUZ",
      "organizationRoleId": 893,
      "roleSpecId": -104,
      "statusId": 1,
      "statusLastChangedDate": "2019-08-04T13:14:44.616Z"
    },
    {
      "extendedData": [
        {
          "characteristicId": "1",
          "characteristicValue": "M049",
          "extendedDataId": "364"
        },
        {
          "characteristicId": "2",
          "characteristicValue": "ST",
          "extendedDataId": "365"
        }
      ],
      "name": "SB - GULF NIGHT TECHNOTEL - LINGAYEN",
      "organizationRoleId": 894,
      "partyRoleAssocs": [
        {
          "partyRoleAssocId": "512"
        }
      ],
      "roleSpecId": -103,
      "statusId": 1,
      "statusLastChangedDate": "2019-08-04T13:14:44.616Z"
    },
    {
      "name": "SLUZ",
      "organizationRoleId": 895,
      "roleSpecId": -104,
      "statusId": 1,
      "statusLastChangedDate": "2019-08-04T13:14:44.616Z"
    },
    {

      "extendedData": [
        {
          "characteristicId": "1",
          "characteristicValue": "M050",
          "extendedDataId": "366"
        },
        {
          "characteristicId": "2",
          "characteristicValue": "ST",
          "extendedDataId": "367"
        }
      ],
      "name": "adfasd",
      "organizationRoleId": 896,
      "partyRoleAssocs": [
        {
          "partyRoleAssocId": "513"
        }
      ],
      "roleSpecId": -103,
      "statusId": 1,
      "statusLastChangedDate": "2019-08-04T13:14:44.616Z"
    }
  ],
  "statusId": 1,
  "statusLastChangedDate": "2019-08-04T10:14:44.697Z",
  "tenantId": "2",
  "type": "organization"
}

}

i have to select all the document where type is organization and any of the "characteristicValue" inside array extended data is "M050". Above is the sample document structure. How can i search inside an array which is also part of another array in couchbase?

I have tried below N1ql query its not working.

SELECT *
FROM `contact`
UNNEST contacts.organizationRoles AS roles
WHERE contacts.type = "organization" 
AND ANY extendedData in roles satisfies 
extendeddata.characteristicValue="M050".

Answer 1

Based on output format you can use one of the following.

SELECT *
FROM `contact` AS c
UNNEST c.organizationRoles AS roles
UNNEST roles.extendedData AS e
WHERE c.type = "organization" AND e.characteristicValue = "M050";

SELECT *
FROM `contact` AS c
UNNEST c.organizationRoles AS roles
WHERE c.type = "organization" AND ANY e IN roles.extendedData SATISFIES e.characteristicValue = "M050" END;

SELECT *
FROM `contact` AS c
WHERE c.type = "organization" AND ANY roles IN c.organizationRoles
                              SATISFIES (ANY e IN roles.extendedData
                                         SATISFIES e.characteristicValue = "M050" END) END;