Couchbase Index and N1QL Query

Question

I have created a new bucket, FooBar on my couchbase server.

I have a Json Document which is a List with some properties and it is in my couchbase bucket as follows:

[
{
"Venue": "Venue1",
"Country": "AU",
"Locale": "QLD"
},
{
"Venue": "Venue2",
"Country": "AU",
"Locale": "NSW"
  },

  {
    "Venue": "Venue3",
    "Country": "AU",
    "Locale": "NSW"
}
]

How Do i get the couchbase query to return a List of Locations when using N1QL query.

For instance, SELECT * FROM FooBar WHERE Locale = 'QLD'

Please let me know of any indexes I would need to create as well. Additionally, how can i return only results where the object is of type Location, and not say another object which may have the 'Locale' Property.

Chud

PS - I have also created some indexes, however I would like an unbiased answer on how to achieve this.

The JSON you're showing is an array of objects. Typically these would each be separate documents. I just wanted to verify you really want to query an array like you've shown before answering. — Hod
Hi Hod, At this stage it is an array. But happy to change over based on your recommendation on the data model. If you could provide both approaches that would be much appreciated. — The_Chud

Hod Hod · Accepted Answer · 2018-05-31T06:06:40

Typically you would store these as separate documents, rather than in a single document as an array of objects, which is how the data is currently shown.

Since you can mix document structures, the usual pattern to distinguish them is to have something like a 'type' field. ('type' is in no way special, just the most common choice.)

So your example would look like:

{
  "Venue": "Venue1",
  "Country": "AU",
  "Locale": "QLD",
  "type": "location"
}
...
{
  "Venue": "Venue3",
  "Country": "AU",
  "Locale": "NSW",
  "type": "location"
}

where each JSON object would be a separate document with a unique document ID. (If you have some predefined data you want to load, look at cbimport for how to add it to your database. There are a few different formats for doing it. You can also have it generate document IDs for you.)

Then, what @vsr wrote is correct. You'd create an index on the Locale field. That will be optimal for the query you want. Note you could create an index on every document with CREATE INDEX ix1 ON FooBar(Locale); too. In this simple case it doesn't really make a difference. Read about the query Explain feature of the admin console to for help using that to understand optimizing queries.

Finally, the query @vsr wrote is also correct:

SELECT * FROM FooBar WHERE type = "Location" AND Locale = "QLD";

Couchbase Index and N1QL Query

2 Answers