Query for field in 2 level nested array

SQL++
1

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.

2 
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
3

That works like a charm. Thanks a lot.