Query for field in 2 level nested array

Hi,

I have a document similar like the one below that is returned with SELECT * FROM configuration WHERE type = 'region'.

[
  {
    "configuration": {
      "licensorName": "Lions Gate",
      "regionList": [
        {
          "countries": [
            {
              "countryCode": "CA",
              "countryName": "Canada"
            },
            {
              "countryCode": "US",
              "countryName": "United States"
            }
          ],
          "regionName": "North America"
        },
        {
          "countries": [
            {
              "countryCode": "AR",
              "countryName": "Argentina"
            },
            {
              "countryCode": "PY",
              "countryName": "Paraguay"
            }
          ],
          "regionName": "South America"
        }
      ],
      "type": "region"
    }
  }
]

What I am trying to do now is to create a query that allows me to return the same data when I search with countryCode. For example, if I have a countryCode = AR, I want to see the same result returned. However, if I search for a countryCode = PU, I get back nothing. It is hard to do as the example that I found in the N1QL tutorial with ANY only helps me to query for items in regionList, not the items inside the regionList's items. Any help would be greatly appreciated here.

SELECT cf.* 
FROM configuration AS cf 
WHERE cf.type = "region" 
       AND ANY  r IN cf.regionList 
           SATISFIES (ANY c IN r.countries 
                       SATISFIES c.countryCode = "AR" 
                       END) 
           END;
1 Like

That works like a charm. Thanks a lot.