THE CURIOUS DEVELOPER

Even though the main purpose of document-based databases is key-value operations, Couchbase still supports more complex SQL style queries with N1QL (nickel). N1QL is a query language which is described as SQL for JSON.

Make sure to check out the other articles in the series as well:

1. NoSQL with Couchbase: Setting everything up with Docker
2. NoSQL with Couchbase: Getting started
3. NoSQL with Couchbase: Working with JSON
4. NoSQL with Couchbase: Querying with N1QL
5. NoSQL with Couchbase: Creating Indexes that Scale

In my latest article, we checked how we can work with JSON in an effective way. In this article, we will still work with JSON but instead of getting JSON documents by key-value, we are going to retrieve them with N1QL.

Preparing our Person class

We start by adding a new field, company, to our Person class. We are going to implement a REST endpoint for retrieving a list of persons working at a company.

package org.thecuriousdev.demo.skeleton.db.domain;

import com.google.common.base.MoreObjects;
import org.thecuriousdev.demo.skeleton.db.CouchbaseDocument;

public class Person implements CouchbaseDocument {

    public static final String DB_TYPE = "tcd:person";

    private String id;
    private long cas;

    private String name;
    private int age;
    private String favouriteFood;
    private String company;

    private String type;


    public Person() {
        this.type = DB_TYPE;
    }

    public Person(String name, int age, String favouriteFood) {
        this.name = name;
        this.age = age;
        this.favouriteFood = favouriteFood;
        this.type = DB_TYPE;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getFavouriteFood() {
        return favouriteFood;
    }

    public void setFavouriteFood(String favouriteFood) {
        this.favouriteFood = favouriteFood;
    }

    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    @Override
    public String getType() {
        return type;
    }

    @Override
    public void setType(String type) {
        this.type = type;
    }

    @Override
    public String getId() {
        return id;
    }

    @Override
    public void setId(String id) {
        this.id = id;
    }

    @Override
    public long getCas() {
        return cas;
    }

    @Override
    public void setCas(long cas) {
        this.cas = cas;
    }

    @Override
    public String toString() {
        return MoreObjects.toStringHelper(this.getClass())
                .add("name", name)
                .add("age", age)
                .add("favouriteFood", favouriteFood)
                .add("company", company)
                .omitNullValues()
                .toString();
    }
}

Checking and handling N1qlQueryResult

Next, I prefer to have some generic class that handles and processes the N1QL queries. A N1QL query returns a N1qlQueryResult which has a couple of interesting fields which are good to inspect. One of them is info which can store some metrics, but this info object doesn’t always exist, but if it does it’s good to at least log it on DEBUG level. Another interesting field is the errors() which returns a list of errors that occurred (if they occurred) in the request. We will notice the importance of this field later in the article. Last but not the least, it also, of course, contains the rows of results if the query went fine.

package org.thecuriousdev.demo.skeleton.util;

import com.couchbase.client.java.query.N1qlQueryResult;
import com.couchbase.client.java.query.N1qlQueryRow;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.thecuriousdev.demo.skeleton.db.DbOperationType;

import java.util.Collections;
import java.util.List;
import java.util.function.Supplier;

public class QueryResultChecker {

    private static final Logger LOG = LoggerFactory.getLogger(QueryResultChecker.class);

    public List processQuery(DbOperationType type, final Supplier supplier) {

        N1qlQueryResult result = supplier.get();

        if (LOG.isTraceEnabled()) {
            if (result.info() != null) {
                    LOG.trace("Metrics from {} contained {}", type, result.info());
            }
        }

        if (result.errors() != null) {
            result.errors().stream().forEach(e -> LOG.warn("Error during {}: {}", type, e));
            throw new RuntimeException("Error occurred during N1QL query");
        }

        return result.allRows() != null ? result.allRows() : Collections.emptyList();
    }
}

Our class QueryResultChecker has so far only one method, processQuery(DbOperationType, Supplier).

The supplier will be the query against the bucket which returns a N1qlQueryResult. If there are any errors in the query, we log it on WARN level.

Deserializing the result

The N1qlQueryResult will include N1qlQueryRows which has a value that is a JsonObject, so when querying by N1QL we do not get a JsonDocument as we got from the key-value operations. This means we have to write another deserialize method that takes a JsonObject. We also need to pass in the bucket name in the constructor in order to deserialize it into our Person class.

public Optional deserialize(JsonDocument doc) {
    try {
      Optional json = Optional.ofNullable(doc.content().toString());

      if (json.isPresent()) {
        T obj = mapper.readValue(json.get(), typeClass);
        obj.setCas(doc.cas());
        obj.setId(doc.id());
        return Optional.of(obj);
      }

    } catch (IOException e) {
      LOGGER.info("Failed to deserialize document {}", doc, e);
    }

    return Optional.empty();
  }

Writing the N1QL query

We are now ready to write the N1QL query statement in our PersonRepository class. We will use parameterized queries and Couchbase expressive domain-specific language (DSL).

select("*, meta().id, meta().cas")
        .from(i(bucket.name()))
        .where(x("company").eq(x("$1"))
                .and("type").eq(s(Person.DB_TYPE))).toString();

This will translate into the following query.

SELECT *, meta().id, meta().cas FROM `thecuriousdev-demo` WHERE company = $1 AND type = "tcd:person"

Our full PersonRepository class looks like the following.

package org.thecuriousdev.demo.skeleton.db;

import static com.couchbase.client.java.query.Select.select;
import static com.couchbase.client.java.query.dsl.Expression.i;
import static com.couchbase.client.java.query.dsl.Expression.s;
import static com.couchbase.client.java.query.dsl.Expression.x;

import com.couchbase.client.java.Bucket;
import com.couchbase.client.java.document.JsonDocument;
import com.couchbase.client.java.document.RawJsonDocument;
import com.couchbase.client.java.document.json.JsonArray;
import com.couchbase.client.java.query.N1qlQuery;
import com.couchbase.client.java.query.N1qlQueryRow;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.thecuriousdev.demo.skeleton.db.domain.Person;
import org.thecuriousdev.demo.skeleton.util.QueryResultChecker;
import org.thecuriousdev.demo.skeleton.util.Serializer;


@Repository
public class PersonRepository {

  private static final Logger LOG = LoggerFactory.getLogger(PersonRepository.class);

  private final Bucket bucket;
  private final Serializer serializer;
  private final QueryResultChecker queryResultChecker;

  private final String peopleWorkingAtCompanyQuery;

  @Autowired
  public PersonRepository(Bucket bucket, ObjectMapper objectMapper) {
    this.bucket = bucket;
    this.serializer = new Serializer<>(objectMapper, Person.class, bucket.name());
    this.queryResultChecker = new QueryResultChecker();

    peopleWorkingAtCompanyQuery = select("*, meta().id, meta().cas")
        .from(i(bucket.name()))
        .where(x("company").eq(x("$1"))
            .and("type").eq(s(Person.DB_TYPE))).toString();
  }

  public Optional findById(String name) {
    Optional doc = Optional.ofNullable(bucket.get(getPersonDocumentId(name)));

    if (doc.isPresent()) {
      return serializer.deserialize(doc.get());
    }

    return Optional.empty();
  }

  public void save(Person person) {
    Optional json = serializer.seralize(person);
    if (json.isPresent()) {
      bucket.upsert(RawJsonDocument
          .create(getPersonDocumentId(person.getName()), json.get(), person.getCas()));
      LOG.info("Saved person : {}", person);
    } else {
      LOG.warn("Failed to save user {}", person);
    }
  }

  public void delete(String name) {
    bucket.remove(name);
    LOG.info("Deleted person: {}", name);
  }

  public List findPeopleWorkingAtCompany(String company) {
    JsonArray params = JsonArray.create().add(company);
    N1qlQuery query = N1qlQuery.parameterized(peopleWorkingAtCompanyQuery, params);

    return queryResultChecker
        .processQuery(DbOperationType.N1QL_QUERY_GET_PERSONS_BY_COMPANY, () -> bucket.query(query))
        .stream()
        .map(N1qlQueryRow::value)
        .map(serializer::deserialize)
        .filter(Optional::isPresent)
        .map(Optional::get)
        .collect(Collectors.toList());
  }

  private String getPersonDocumentId(String name) {
    return ":" + Person.DB_TYPE + ":" + name;
  }
}

Because we use a parameterized statement, we also need to provide a JsonArray with the values that we want to insert, and they need to be in the right order.

The DbOperationType is simply just an enum that is used for the logging to be able to figure out which query any potential metrics and errors come from.

REST endpoint

Let’s try out our query by creating up the REST endpoint for querying which persons work at the provided company.

@GetMapping("/person")
public ResponseEntity> getPersonsWorkingAtCompanies(
      @RequestParam @NotNull String company) {
    List persons = personRepository.findPeopleWorkingAtCompany(company);
    return ResponseEntity.ok(persons);
}

We start by inserting a person with a company by running a POST request to http://localhost:9000/person with body:

{
    "name" : "Viktor",
    "age" : 24,
    "favouriteFood" : "tacos",
    "company" : "starbucks"
}

Then we run a GET request to http://localhost:9000/person?company=starbucks.

Hmm, we didn’t receive anything. Let’s check our logs.

Error during N1QL_QUERY_GET_PERSONS_BY_COMPANY: {“msg”:”No index available on keyspace thecuriousdev-demo that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.”,”code”:4000}

Indexes

Well, at least our checking for errors seems to work well. The message explains very well what is missing. We are missing an index to support our query. Let’s do the easiest possible fix; adding a primary index. Open up the GUI for the Couchbase and go into the Query workbench and run the following statement:

CREATE PRIMARY INDEX ON `thecuriousdev-demo`

A primary index is quite good to have in the early stages of development because it lets you run ad-hoc queries. However, it is not recommended for production or the later part of your development process when you are trying to optimize the performance of your application. A primary index is basically the equivalent to full table scans in the SQL world.

Running GET request once again now returns:

[
    {
        "name": "Viktor",
        "age": 24,
        "favouriteFood": "tacos",
        "company": "starbucks",
        "type": "tcd:person"
    }
]

Final words

We have looked at how we can run N1QL queries through the Couchbase Java SDK. We have looked a bit at some good methods that you should inspect and a good way to make handling of N1QL queries more generic which is nice because your application will probably have a couple of those.

The code for this article can be found here.

In the next article, we will dive a bit deeper on indexes. We will remove our primary index and create a more appropriate index that will be more performant. We will also go through some best practices when it comes to indexes.