Retrieving documents

In this section, you’re going to look at two methods of retrieving documents from a collection: you will use the administration console to build simple queries, and also write a short program to retrieve documents matching certain criteria. Both of the methods will introduce SQL++, Couchbase’s SQL-based query language.

Using the Query Editor

Return to the admin console, and click on the Query item on the left-hand menu.

This will take you to the query workbench. The workbench has a few filter fields that’ll make it much easier to narrow down our search criteria.

Use the two dropdown items to select the student-bucket and the art-school-scope. This narrows the scope of the queries, meaning you don’t have to add the name of the bucket and the scope to your queries.

Okay, let’s try a simple query to retrieve all the course in our collection.

Type the following query into the query editor field:

select crc.* from `course-record-collection` crc

SQL++ is very similar to standard SQL. Once you have mastered the document database model, you’ll find it very easy to adapt.

What happens when you hit Execute?

You should get a result:

[
  {
    "course-name": "art history",
    "credit-points": 100,
    "faculty": "fine art"
  },
  {
    "course-name": "fine art",
    "credit-points": 50,
    "faculty": "fine art"
  },
  {
    "course-name": "graphic design",
    "credit-points": 200,
    "faculty": "media and communication"
  }
]

Creating an index

To improve the speed of your queries, you can use the query editor to add an index to the bucket. To do this, enter the following command into the editor and press Enter.

create primary index course_idx on `course-record-collection`

This will create a single index (course_idx) on your course-record-collection.

Okay, now try something else: returning the courses with credit-points of less than 200:

select crc.* from `course-record-collection` crc where crc.`credit-points` < 200

which will bring back:

[
  {
    "course-name": "art history",
    "credit-points": 100,
    "faculty": "fine art"
  },
  {
    "course-name": "fine art",
    "credit-points": 50,
    "faculty": "fine art"
  }
]

But what about the primary id field?

Good question. You may want to get hold of id field, which as you can see, isn’t returned with the document, even if we’re asking for all the fields in the call. The primary key exists as part of the document’s "meta" structure, which can be interrogated along with the rest of the document. Make the following small adjustment to the SQL++ statement and run the query again:

select META().id, crc.* from `course-record-collection` crc where crc.`credit-points` < 200

The META() function call will return any property contained in the document’s metadata, including its id:

[
  {
    "course-name": "art history",
    "credit-points": 100,
    "faculty": "fine art",
    "id": "ART-HISTORY-000001"    (1)
  },
  {
    "course-name": "fine art",
    "credit-points": 50,
    "faculty": "fine art",
    "id": "FINE-ART-000002"    (1)
  }
]

1	id fields added to the returned document.

You can find a full rundown of the SQL++ language here: SQL++ for Query Reference.

Using the SDK

Of course, you can also retrieve documents using the JDK. In this section, you’re going to use the same SQL++ queries as part of a small Java application. Let’s start with a basic record retrieval:

import com.couchbase.client.core.error.CouchbaseException;
import com.couchbase.client.java.Cluster;
import com.couchbase.client.java.json.JsonObject;
import com.couchbase.client.java.query.QueryResult;

public class ArtSchoolRetriever {

    public static void main(String[] args) {
        Cluster cluster = Cluster.connect("localhost",
                "Administrator", "password");

        retrieveCourses(cluster);

        cluster.disconnect();
    }

    private static void retrieveCourses(Cluster cluster) {

        try {
            final QueryResult result = cluster.query("select crc.* from `student-bucket`.`art-school-scope`.`course-record-collection` crc");

            for (JsonObject row : result.rowsAsObject()) {
                System.out.println("Found row: " + row);
            }

        } catch (CouchbaseException ex) {
            ex.printStackTrace();
        }
    }
}

If you build and run this program:

mvn exec:java -Dexec.mainClass="ArtSchoolRetriever" -Dexec.cleanupDaemonThreads=false

Then you’ll get a list of the classes in the output.

Terminal window showing course records retrieved with the Java SDK

You may have noticed a difference between the SQL statement we used in the web console, and the statement used as part of the application:

select crc.* from `student-bucket`.`art-school-scope`.`course-record-collection` crc

The name of the collection in the SQL++ statement has to be fully qualified, including the name of the bucket as well as the containing scope.

You can, of course, set parameters as part of your query, as shown in the next example:

import com.couchbase.client.core.error.CouchbaseException;
import com.couchbase.client.java.Cluster;
import com.couchbase.client.java.json.JsonObject;
import com.couchbase.client.java.query.QueryOptions;
import com.couchbase.client.java.query.QueryResult;

public class ArtSchoolRetrieverParameters {

    public static void main(String[] args) {
        Cluster cluster = Cluster.connect("localhost",
                "Administrator", "password");

        retrieveCourses(cluster);

        cluster.disconnect();
    }

    private static void retrieveCourses(Cluster cluster) {

        try {
            final QueryResult result = cluster.query("select crc.* " +
                            "from `student-bucket`.`art-school-scope`.`course-record-collection` crc " +
                            "where crc.`credit-points` < $creditPoints",    (1)
                    QueryOptions.queryOptions()
                            .parameters(JsonObject.create().put("creditPoints", 200)));    (2)

            for (JsonObject row : result.rowsAsObject()) {
                System.out.println("Found row: " + row);
            }

        } catch (CouchbaseException ex) {
            ex.printStackTrace();
        }
    }
}

1	The SQL++ statement takes a parameters `$creditPoints` which will be substituted with a correctly typed value when the statement is called.
2	The value to substitute is provided in the `QueryOptions` given as the second parameter in the call. The value of the map entry is the actual parameter value (in this case, 200 which we’re using to test the `credit-points`).

You can use maven to run the program:

mvn exec:java -Dexec.mainClass="ArtSchoolRetrieverParameters" -Dexec.cleanupDaemonThreads=false

Next steps

Now you can add and search for records, the next section will consolidate what you’ve learned so far by demonstrating how to amend existing records by adding enrollment details. So when you’re ready carry on to Adding Course Enrollments.