DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Perform Various N1QL Queries Without Indexes in Couchbase Server

Perform Various N1QL Queries Without Indexes in Couchbase Server

You may be surprised to learn that not every N1QL query requires an index to exist. Learn how to run a few N1QL queries on a Couchbase Bucket that has no indexes.

Nic Raboy user avatar by
Nic Raboy
·
May. 03, 17 · Tutorial
Like (0)
Save
Tweet
Share
5.47K Views

Join the DZone community and get the full member experience.

Join For Free

As you probably already know, you’re able to query Couchbase NoSQL documents using a SQL dialect called N1QL. This is made possible through indexes that you create on documents in your Couchbase Buckets. However, what if I told you that not every N1QL query requires an index to first exist? After talking with my colleague, Justin Michaels, he showed me an awesome trick to perform bulk operations in N1QL without indexes. This was news to me because I always thought you needed at least one index to exist, but hey, you learn something new every day.

We’re going to see how to run a few N1QL queries on a Couchbase Bucket that has no indexes and that includes no primary index.

Before we jump into some sample scenarios, you might be wondering how it is possible to run queries without an index. This is actually possible by making use of the USE KEYS operator to hone in on specific documents by their key that exists in the meta information.

Take the following document for example:

{
    "type": "person",
    "firstname": "Nic",
    "lastname": "Raboy",
    "social_media": [
        {
            "website": "https://www.thepolyglotdeveloper.com"
        }
    ]
}

Above, we have a simple document that represents a particular person. Let’s say the above document has nraboy as the ID value. To make things interesting, let’s create another document.

Assume the following has mraboy as the ID value:

{
    "type": "person",
    "firstname": "Maria",
    "lastname": "Raboy",
    "social_media": [
        {
            "website": "https://www.mraboy.com"
        }
    ]
}

So, if we wanted to query either of these two documents with the USE KEYS operator in N1QL, we could compose a query that looks like the following:

SELECT * 
FROM example 
USE KEYS ["nraboy", "mraboy"];

If you look at the EXPLAIN of the above query, you’ll notice that no index was used in the query. The above type of query would be useful if you knew the keys that you wanted to obtain and wanted incredibly fast performance similar to how it was done in a previous article I wrote, Getting Multiple Documents by Key in a Single Operation Eith Node.js.

Let’s make things a bit more complicated. What if we wanted to query with a relationship on one or more of the document properties?

Let’s create another document with couchbase as the document ID:

{
    "type": "company",
    "name": "Couchbase Inc",
    "address": {
      "city": "Mountain View",
      "state": "CA"
    }
}

The above document represents a company. As you probably guessed, we’re going to query for the company information of each person. To make this possible, let’s change the nraboy document to look like the following:

{
    "type": "person",
    "firstname": "Nic",
    "lastname": "Raboy",
    "social_media": [
        {
            "website": "https://www.thepolyglotdeveloper.com"
        }
    ],
    "company": "couchbase"
}

Notice we’ve added a property with the key to our other document. We won’t add any company information to the mraboy document.

Take the following query that has a multiple document relationship, but no indexes created:

SELECT
    p.firstname,
    p.lastname,
    (SELECT c.* FROM example c USE KEYS p.company)[0] AS company
FROM example p 
USE KEYS ["nraboy", "mraboy"];

Notice that the above query has a subquery that also uses the USE KEYS operator. Not bad right? Try using other operators like UNNEST to flatten the array data found in the social_media property.

Conclusion

You just saw how to write N1QL queries in Couchbase that use no index. By using the USE KEYS operator we can do bulk operations based on key, like I demonstrated in the articles Getting Multiple Documents by Key in a Single Operation With Node.js and Using Golang to Get Multiple Couchbase Documents by Key in a Single Operation. A huge thanks to Justin Michaels from Couchbase for helping me with this.

Database Document Couchbase Server

Published at DZone with permission of Nic Raboy, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • How to Submit a Post to DZone
  • How To Check Docker Images for Vulnerabilities
  • The Quest for REST
  • API Design Patterns Review

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: