N1QL Queries from the SDK

    +
    You can query for documents in Couchbase using the N1QL query language, a language based on SQL, but designed for structured and flexible JSON documents. Querying can solve typical programming tasks such as finding a user profile by email address, Facebook login, or user ID.

    Our query service uses N1QL, which will be fairly familiar to anyone who’s used any dialect of SQL. Additional resources for learning about N1QL are listed at the bottom of the page.

    Before you get started you may wish to check out the N1QL intro page, or just dive in with a query against our travel-sample data set. To make a Bucket queryable, it must have at least one index defined. You can define a primary index on a bucket. While not recommended for production, defining a primary index is useful for ad hoc queries that are great for exploring your data, the reason they are not recommended for production is that they enable a full-bucket scan.

    Use cbq, our interactive Query shell. Open it, and enter the following:

    CREATE PRIMARY INDEX ON `travel-sample`

    By replacing travel-sample with a different Bucket, you can build an index on your bucket of choice.

    The default installation places cbq in /opt/couchbase/bin/ on Linux, /Applications/Couchbase Server.app/Contents/Resources/couchbase-core/bin/cbq on OS X, and C:\Program Files\Couchbase\Server\bin\cbq.exe on Microsoft Windows.

    Queries & Placeholders

    Placeholders allow you to specify variable constraints for an otherwise constant query. There are two variants of placeholders: positional and named parameters. Positional parameters use an ordinal placeholder for substitution and named parameters use variables. A named or positional parameter is a placeholder for a value in the WHERE, LIMIT, or OFFSET clause of a query. Note that both parameters and options are optional.

    Positional parameter example:
    async function queryPlaceholders() {
      const query = `
        SELECT airportname, city FROM \`travel-sample\`
        WHERE type=$1
          AND city=$2
      `;
      const options = { parameters: ['airport', 'San Jose'] }
    
      try {
        let result = await cluster.query(query, options)
        console.log("Result:", result)
        return result
      } catch (error) {
        console.error('Query failed: ', error)
      }
    }
    Named parameter example:
    async function queryNamed() {
      const query = `
        SELECT airportname, city FROM \`travel-sample\`
        WHERE type=$TYPE
          AND city=$CITY;
      `
      const options = { parameters: { TYPE: 'airport', CITY: 'Reno' } }
    
      try {
        let result = await cluster.query(query, options)
        console.log("Result:", result)
        return result
      } catch (error) {
        console.error('Query failed: ', error)
      }
    }

    Handling Results

    Most queries return more than one result, and you want to iterate over the results:

    async function queryResults() {
      const query = `
      SELECT airportname, city FROM \`travel-sample\`
      WHERE type='airport'
        AND tz LIKE '%Los_Angeles'
        AND airportname LIKE '%Intl';
      `
      try {
        let results = await cluster.query(query);
        results.rows.forEach((row) => {
          console.log('Query row: ', row)
        })
        return results
      } catch (error) {
        console.error('Query failed: ', error)
      }
    }

    CAS and N1QL

    If you are performing an operation with N1QL that requires CAS to be used, in combination with using CAS from regular KV operations for example, then you need to be aware of the CAS type. CAS is stored as a 64-bit integer, which cannot be represented safely in javaScript — thus you must convert to a string:

      const GET_IDS = `
        SELECT  META().id AS recordId
              , TOSTRING(META().cas) AS cas
              , id
        FROM cdb
        WHERE type = 'profile'
        LIMIT $count
        `;

    Additional Resources

    N1QL is not the only query option in Couchbase. Be sure to check that your use case fits your selection of query service.

    The N1QL Language Reference introduces up a complete guide to the N1QL language, including all of the latest additions.

    The N1QL interactive tutorial is a good introduction to the basics of N1QL use.