CODEWITHSUNDEEP
couchbasesql++couchbase-view
The_Chud
The_Chud

Reputation: 1091

Couchbase Index and N1QL Query

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.

Upvotes: 1

Views: 1288

Answers (2)

Hod
Hod

Reputation: 2276

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";

Upvotes: 2

vsr
vsr

Reputation: 7414

CREATE INDEX ix1 ON FooBar(Locale);

https://dzone.com/articles/designing-index-for-query-in-couchbase-n1ql

CREATE INDEX ix1 ON  FooBar(Locale) WHERE type = "Location";
SELECT * FROM FooBar WHERE type = "Location" AND Locale = "QLD";

If it is array and filed name is list

CREATE INDEX ix1 ON  FooBar(DISTINCT ARRAY v.Locale FOR v IN list END) WHERE type = "Location";
SELECT * FROM FooBar WHERE type = "Location" AND ANY v IN list SATISFIES v.Locale = "QLD" END;

Upvotes: 1

Related Questions