CODEWITHSUNDEEP
jsoncouchbasesql++
zwornik
zwornik

Reputation: 359

N1QL - query multilevel nested JSON data in CouchBase

How do I select the whole JSON document by querying for multi level nested values? Here is example of my JSON:

{
  "id": "6316194187233559482",
  "type": "Hotel",
  "attributes": [
    {
      "country": "Germany",
      "officialNames": [
          {
            "name": "The Ritz-Carlton, Berlin",
            "language": "GER"
          }
      ],
      "streetsAndCities": [
          {
              "city": "Berlin",
              "cityLanguage": "GER",
              "street": "Potsdamer Platz ",
              "streetLanguage": "GER"
          }
      ]   
    }
  ]
}

And I want to query it for "name" attribute. I have experimented with UNNEST function and I have no problem to acces first level of nested data i.e. "officialNames". But I don't know how to access "name" attribute. Referencing it by position e.g.:

SELECT * FROM document 
UNNEST attributes as attributes
WHERE attributes[0].officialNames[0].name = "The Ritz-Carlton, Berlin"

Is not going to work as position of each attribute in JSON can change.

Upvotes: 3

Views: 1038

Answers (1)

Johan Larson
Johan Larson

Reputation: 1890

What do you mean by "position of each attribute in JSON can change"? Do you mean that the "attributes" and "officialNames" and "streetsAndCities" arrays can have additional entries and you want to search through all the entries?

If yes, then have a look at the ANY operator here: https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/collectionops.html

Upvotes: 3

Related Questions