SQL++ Queries from the SDK

how-to

You can query for documents in Couchbase using the SQL++ query language (formerly N1QL), 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.

Getting Started

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

Before you get started you may wish to check out the SQL++ intro page, or just dive in with a query against our travel-sample data set.

After familiarizing yourself with the basics on how the SQL++ query language works and how to query it from the UI you can use it from the Node.js SDK.

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\`.inventory.airport
  WHERE city=$1
  `;
  const options = { parameters: ['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\`.inventory.airport
    WHERE city=$CITY;
  `
  const options = { parameters: { 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\`.inventory.airport
  WHERE 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 SQL++

If you are performing an operation with SQL++ 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
    `;

Querying the default Scope

When working with earlier versions (before the Developer Preview in 6.5), or with other server versions, the defaultcollection is used from the SDK, by simply addressing the Bucket itself.

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)
  }
}

Additional Resources

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

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

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