Result Sets

      +

      Description — How to use Couchbase Lite Query’s Result Sets
      Related Content — QueryBuilder | SQL++ for Mobile | Predictive Queries | Live Queries | Indexing

      Query Execution

      The execution of a Couchbase Lite for Java’s database query returns an array of results, a result set.

      Each row of the result set represents the data returned from a document that met the conditions defined by the WHERE statement of your query. The composition of each row is determined by the SelectResult expressions provided in the SELECT statement.

      Returned Results

      The types of SelectResult formats you may encounter include those generated by :

      • QueryBuilder.select(SelectResult.all()) — Using All

      • QueryBuilder.select(SelectResult.expression(Meta.id)) — Using Doc Id Metadata such as the _id

      • QueryBuilder.select(SelectResult.property("myProp")) — Using Specific Properties

      Return All Document Properties

      The SelectResult returned by SelectResult.all() is a dictionary object, with the database name as the key and the document properties as an array of key-value pairs

      Example 1. Returning All Properties
      [
        {
          "travel-sample": { (1)
            "callsign": "MILE-AIR",
            "country": "United States",
            "iata": "Q5",
            "icao": "MLA",
            "id": 10,
            "name": "40-Mile Air",
            "type": "airline"
          }
        },
        {
          "travel-sample": { (2)
            "callsign": "ALASKAN-AIR",
            "country": "United States",
            "iata": "AA",
            "icao": "AAA",
            "id": 10,
            "name": "Alaskan Airways",
            "type": "airline"
          }
        }
      ]

      Return Document Id Only

      The SelectResult returned by queries using a SelectResult expression of the form SelectResult.expression(Meta.id) comprises a dictionary object with ID as the key and the ID value as the value.

      Example 2. Returning Meta Properties — Document ID
      [
        {
          "id": "hotel123"
        },
        {
          "id": "hotel456"
        },
      ]

      Return Specific Properties Only

      The SelectResult returned by queries using one or more SelectResult expressions of the form SelectResult.expression(property("name")) ) comprises a key-value pair for each SelectResult expression in the query. The key being the property name.

      Example 3. Returning Specific Properties
      [
        { (1)
          "id": "hotel123",
          "type": "hotel",
          "name": "Hotel Ghia"
        },
        { (2)
          "id": "hotel456",
          "type": "hotel",
          "name": "Hotel Deluxe",
        }
      ]

      Processing Results

      To retrieve the results of your query, you need to execute it using Query.execute.

      The output from the execution is an array, with each array element representing the data from a document that matched your search criteria.

      To unpack the results you need to iterate through this array. Alternatively, you can convert the result to a JSON string — see:

      Access Document Properties - All Properties

      Here we look at how to access document properties when you have used SelectResult.all.

      In this case each array element is a dictionary structure with the database name as its key. The properties are presented in the value as an array of key-value pairs (property name/property value).

      You access the retrieved document properties by converting each row’s value, in turn, to a dictionary — as shown in Example 4.

      Example 4. Access All Properties
      Map<String, Hotel> hotels = new HashMap<>();
      try (ResultSet resultSet = listQuery.execute()) {
          for (Result result: resultSet) {
              // get the k-v pairs from the 'hotel' key's value into a dictionary
              Dictionary docsProp = result.getDictionary(0); (1)
              String docsId = docsProp.getString("id");
              String docsName = docsProp.getString("Name");
              String docsType = docsProp.getString("Type");
              String docsCity = docsProp.getString("City");
      
              // Alternatively, access results value dictionary directly
              final Hotel hotel = new Hotel();
              hotel.setId(result.getDictionary(0).getString("id")); (2)
              hotel.setType(result.getDictionary(0).getString("Type"));
              hotel.setName(result.getDictionary(0).getString("Name"));
              hotel.setCity(result.getDictionary(0).getString("City"));
              hotel.setCountry(result.getDictionary(0).getString("Country"));
              hotel.setDescription(result.getDictionary(0).getString("Description"));
              hotels.put(hotel.getId(), hotel);
          }
      }
      1 Here we get the dictionary of document properties using the database name as the key. You can add this dictionary to an array of returned matches, for processing elsewhere in the app.
      2 Alternatively, you can access the document properties here, by using the property names as keys to the dictionary object.

      Access Document Properties - ID

      Here we look at how to access document properties when you have returned only the document IDs for documents that matched your selection criteria.

      This is something you may do when retrieval of the properties directly by the query may consume excessive amounts of memory and-or processing time.

      In this case each array element is a dictionary structure where ID is the key and the required document ID is the value.

      Access the required document properties by retrieving the document from the database using its document ID — as shown in Example 5.

      Example 5. Access by ID
      try (ResultSet rs = listQuery.execute()) {
          for (Result result: rs.allResults()) {
      
              // get the ID form the result's k-v pair array
              String thisDocsId = result.getString("metaID"); (1)
      
              // Get document from DB using retrieved ID
              Document thisDoc = collection.getDocument(thisDocsId);
      
              // Process document as required
              String thisDocsName = thisDoc.getString("Name");
          }
      }
      1 Extract the Id value from the dictionary and use it to get the document from the database

      Access Document Properties - Selected Properties

      Here we look at how to access properties when you have used SelectResult to get a specific subset of properties.

      In this case each array element is an array of key value pairs (property name/property value).

      Access the retrieved properties by converting each row into a dictionary — as shown in [ex-acc-specific].

      HashMap<String, Hotel> hotels = new HashMap<>();
      try (ResultSet resultSet = listQuery.execute()) {
          for (Result result: resultSet) {
      
              // get data direct from result k-v pairs
              final Hotel hotel = new Hotel();
              hotel.setId(result.getString("id"));
              hotel.setType(result.getString("Type"));
              hotel.setName(result.getString("Name"));
              hotel.setCity(result.getString("City"));
      
              // Store created hotel object in a hashmap of hotels
              hotels.put(hotel.getId(), hotel);
      
              // Get result k-v pairs into a 'dictionary' object
              Map<String, Object> thisDocsProps = result.toMap();
              String docId =
                  thisDocsProps.getOrDefault("id", null).toString();
              String docName =
                  thisDocsProps.getOrDefault("Name", null).toString();
              String docType =
                  thisDocsProps.getOrDefault("Type", null).toString();
              String docCity =
                  thisDocsProps.getOrDefault("City", null).toString();
          }
      }

      JSON Result Sets

      Example 6. Using JSON Results

      Use Result.toJSON() to transform your result string into a JSON string, which can easily be serialized or used as required in your application. See <> for a working example.

              ObjectMapper mapper = new ObjectMapper();
              ArrayList<Hotel> hotels = new ArrayList<>();
              HashMap<String, Object> dictFromJSONstring;
      
              try (ResultSet resultSet = listQuery.execute()) {
                  for (Result result: resultSet) {
      
                      // Get result as JSON string
                      String thisJsonString = result.toJSON(); (1)
      
                      // Get Java  Hashmap from JSON string
                      dictFromJSONstring =
                          mapper.readValue(thisJsonString, HashMap.class); (2)
      
      
                      // Use created hashmap
                      String hotelId = dictFromJSONstring.get("id").toString();
                      String hotelType = dictFromJSONstring.get("type").toString();
                      String hotelname = dictFromJSONstring.get("name").toString();
      
      
                      // Get custom object from Native 'dictionary' object
                      Hotel thisHotel =
                          mapper.readValue(thisJsonString, Hotel.class); (3)
                      hotels.add(thisHotel);
                  }
              }
              // Uses Jackson JSON processor
              ObjectMapper mapper = new ObjectMapper();
              List<Hotel> hotels = new ArrayList<>();
      
              try (ResultSet rs = listQuery.execute()) {
                  for (Result result: rs) {
                      String json = result.toJSON();
                      Map<String, String> dictFromJSONstring = mapper.readValue(json, HashMap.class);
      
                      String hotelId = dictFromJSONstring.get("id");
                      String hotelType = dictFromJSONstring.get("type");
                      String hotelname = dictFromJSONstring.get("name");
      
                      // Get custom object from JSON string
                      Hotel thisHotel = mapper.readValue(json, Hotel.class);
                      hotels.add(thisHotel);
                  }
              }
          }
      
          public List<Map<String, Object>> docsOnlyQuerySyntaxN1QL(Database thisDb) throws CouchbaseLiteException {
              // For Documentation -- N1QL Query using parameters
              //  Declared elsewhere: Database thisDb
              Query thisQuery =
                  thisDb.createQuery(
                      "SELECT META().id AS thisId FROM _ WHERE type = \"hotel\""); (4)
              List<Map<String, Object>> results = new ArrayList<>();
              try (ResultSet rs = thisQuery.execute()) {
                  for (Result result: rs) { results.add(result.toMap()); }
              }
              return results;
          }
      
          public List<Map<String, Object>> docsonlyQuerySyntaxN1QLParams(Database thisDb) throws CouchbaseLiteException {
              // For Documentation -- N1QL Query using parameters
              //  Declared elsewhere: Database thisDb
      
              Query thisQuery =
                  thisDb.createQuery(
                      "SELECT META().id AS thisId FROM _ WHERE type = $type"); // <.
      
              thisQuery.setParameters(
                  new Parameters().setString("type", "hotel")); (5)
      
              List<Map<String, Object>> results = new ArrayList<>();
              try (ResultSet rs = thisQuery.execute()) {
                  for (Result result: rs) { results.add(result.toMap()); }
              }
              return results;
          }
      }
      
      //
      // Copyright (c) 2023 Couchbase, Inc All rights reserved.
      //
      // Licensed under the Apache License, Version 2.0 (the "License");
      // you may not use this file except in compliance with the License.
      // You may obtain a copy of the License at
      //
      // http://www.apache.org/licenses/LICENSE-2.0
      //
      // Unless required by applicable law or agreed to in writing, software
      // distributed under the License is distributed on an "AS IS" BASIS,
      // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
      // See the License for the specific language governing permissions and
      // limitations under the License.
      //
      package com.couchbase.codesnippets;
      
      import androidx.annotation.NonNull;
      
      import java.net.URI;
      import java.net.URISyntaxException;
      import java.security.KeyStore;
      import java.security.KeyStoreException;
      import java.security.cert.X509Certificate;
      import java.util.HashMap;
      import java.util.Map;
      import java.util.Set;
      
      import com.couchbase.codesnippets.utils.Logger;
      import com.couchbase.lite.BasicAuthenticator;
      import com.couchbase.lite.Collection;
      import com.couchbase.lite.CollectionConfiguration;
      import com.couchbase.lite.CouchbaseLiteException;
      import com.couchbase.lite.Database;
      import com.couchbase.lite.DatabaseEndpoint;
      import com.couchbase.lite.DocumentFlag;
      import com.couchbase.lite.Endpoint;
      import com.couchbase.lite.ListenerToken;
      import com.couchbase.lite.ReplicatedDocument;
      import com.couchbase.lite.Replicator;
      import com.couchbase.lite.ReplicatorConfiguration;
      import com.couchbase.lite.ReplicatorProgress;
      import com.couchbase.lite.ReplicatorStatus;
      import com.couchbase.lite.ReplicatorType;
      import com.couchbase.lite.SessionAuthenticator;
      import com.couchbase.lite.URLEndpoint;
      
      
      @SuppressWarnings({"unused"})
      public class ReplicationExamples {
          private Replicator thisReplicator;
          private ListenerToken thisToken;
      
          public void activeReplicatorExample(Set<Collection> collections)
              throws URISyntaxException {
              // Create replicator
              // Consider holding a reference somewhere
              // to prevent the Replicator from being GCed
              Replicator repl = new Replicator( (6)
      
                  // initialize the replicator configuration
                  new ReplicatorConfiguration(new URLEndpoint(new URI("wss://listener.com:8954"))) (7)
                      .addCollections(collections, null)
      
                      // Set replicator type
                      .setType(ReplicatorType.PUSH_AND_PULL)
      
                      // Configure Sync Mode
                      .setContinuous(false) // default value
      
      
                      // set auto-purge behavior
                      // (here we override default)
                      .setAutoPurgeEnabled(false) (8)
      
      
                      // Configure Server Authentication --
                      // only accept self-signed certs
                      .setAcceptOnlySelfSignedServerCertificate(true) (9)
      
                      // Configure the credentials the
                      // client will provide if prompted
                      .setAuthenticator(new BasicAuthenticator("Our Username", "Our Password".toCharArray())) (10)
      
              );
      
              // Optionally add a change listener (11)
              ListenerToken token = repl.addChangeListener(change -> {
                  CouchbaseLiteException err = change.getStatus().getError();
                  if (err != null) { Logger.log("Error code :: " + err.getCode(), err); }
              });
      
              // Start replicator
              repl.start(false); (12)
      
      
              thisReplicator = repl;
              thisToken = token;
      
          }
      
          public void replicatorSimpleExample(Set<Collection> collections) throws URISyntaxException {
              Endpoint theListenerEndpoint
                  = new URLEndpoint(new URI("wss://10.0.2.2:4984/db")); (13)
      
              ReplicatorConfiguration thisConfig =
                  new ReplicatorConfiguration(theListenerEndpoint) (14)
                      .addCollections(collections, null) // default configuration
      
                      .setAcceptOnlySelfSignedServerCertificate(true) (15)
                      .setAuthenticator(new BasicAuthenticator(
                          "valid.user",
                          "valid.password".toCharArray())); (16)
      
              Replicator repl = new Replicator(thisConfig); (17)
              // Start the replicator
              repl.start(); (18)
              // (be sure to hold a reference somewhere that will prevent it from being GCed)
              thisReplicator = repl;
      
          }
      
          public void replicationBasicAuthenticationExample(
              Set<Collection> collections,
              CollectionConfiguration collectionConfig)
              throws URISyntaxException {
      
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, collectionConfig)
                      .setAuthenticator(new BasicAuthenticator("username", "password".toCharArray())));
      
              repl.start();
              thisReplicator = repl;
          }
      
      
          public void replicationSessionAuthenticationExample(
              Set<Collection> collections,
              CollectionConfiguration collectionConfig)
              throws URISyntaxException {
      
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, collectionConfig)
                      .setAuthenticator(new SessionAuthenticator("904ac010862f37c8dd99015a33ab5a3565fd8447")));
      
              repl.start();
              thisReplicator = repl;
          }
      
          public void replicationCustomHeaderExample(
              Set<Collection> collections,
              CollectionConfiguration collectionConfig)
              throws URISyntaxException {
              Map<String, String> headers = new HashMap<>();
              headers.put("CustomHeaderName", "Value");
      
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, collectionConfig)
                      .setHeaders(headers));
      
              repl.start();
              thisReplicator = repl;
          }
      
          public void replicationPushFilterExample(Set<Collection> collections) throws URISyntaxException {
              CollectionConfiguration collectionConfig = new CollectionConfiguration()
                  .setPushFilter((document, flags) -> flags.contains(DocumentFlag.DELETED)); (1)
      
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, collectionConfig));
      
              repl.start();
              thisReplicator = repl;
          }
      
      
          public void replicationPullFilterExample(Set<Collection> collections) throws URISyntaxException {
              CollectionConfiguration collectionConfig = new CollectionConfiguration()
                  .setPullFilter((document, flags) -> "draft".equals(document.getString("type"))); (1)
      
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, collectionConfig));
      
              repl.start();
              thisReplicator = repl;
          }
      
          public void replicationResetCheckpointExample(Set<Collection> collections) throws URISyntaxException {
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, null));
      
              repl.start(true);
      
              // ... at some later time
      
              repl.stop();
          }
      
          public void handlingNetworkErrorsExample(Set<Collection> collections) throws URISyntaxException {
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, null));
      
              repl.addChangeListener(change -> {
                  CouchbaseLiteException error = change.getStatus().getError();
                  if (error != null) { Logger.log("Error code:: " + error); }
              });
              repl.start();
              thisReplicator = repl;
          }
      
          public void certificatePinningExample(Set<Collection> collections, String keyStoreName, String certAlias)
              throws URISyntaxException, KeyStoreException {
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, null)
                      .setPinnedServerX509Certificate(
                          (X509Certificate) KeyStore.getInstance(keyStoreName).getCertificate(certAlias)));
      
              repl.start();
              thisReplicator = repl;
          }
      
          public void replicatorConfigExample(Set<Collection> collections) throws URISyntaxException {
              // initialize the replicator configuration
              ReplicatorConfiguration thisConfig = new ReplicatorConfiguration(
                  new URLEndpoint(new URI("wss://10.0.2.2:8954/travel-sample"))) (19)
                  .addCollections(collections, null);
          }
      
      
          public void p2pReplicatorStatusExample(Replicator repl) {
              ReplicatorStatus status = repl.getStatus();
              ReplicatorProgress progress = status.getProgress();
              Logger.log(
                  "The Replicator is " + status.getActivityLevel()
                      + "and has processed " + progress.getCompleted()
                      + " of " + progress.getTotal() + " changes");
          }
      
      
          public void p2pReplicatorStopExample(Replicator repl) {
              // Stop replication.
              repl.stop(); (20)
          }
      
      
          public void customRetryConfigExample(Set<Collection> collections) throws URISyntaxException {
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, null)
                      //  other config as required . . .
                      .setHeartbeat(150) (21)
                      .setMaxAttempts(20) (22)
                      .setMaxAttemptWaitTime(600)); (23)
      
              repl.start();
              thisReplicator = repl;
          }
      
          public void replicatorDocumentEventExample(Set<Collection> collections) throws URISyntaxException {
              // Create replicator (be sure to hold a reference somewhere that will prevent the Replicator from being GCed)
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollections(collections, null));
      
      
              ListenerToken token = repl.addDocumentReplicationListener(replication -> {
                  Logger.log("Replication type: " + ((replication.isPush()) ? "push" : "pull"));
                  for (ReplicatedDocument document: replication.getDocuments()) {
                      Logger.log("Doc ID: " + document.getID());
      
                      CouchbaseLiteException err = document.getError();
                      if (err != null) {
                          // There was an error
                          Logger.log("Error replicating document: ", err);
                          return;
                      }
      
                      if (document.getFlags().contains(DocumentFlag.DELETED)) {
                          Logger.log("Successfully replicated a deleted document");
                      }
                  }
              });
      
      
              repl.start();
              thisReplicator = repl;
      
              token.remove();
          }
      
          public void replicationPendingDocumentsExample(Collection collection)
              throws CouchbaseLiteException, URISyntaxException {
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(new URI("ws://localhost:4984/mydatabase")))
                      .addCollection(collection, null)
                      .setType(ReplicatorType.PUSH));
      
              Set<String> pendingDocs = repl.getPendingDocumentIds(collection);
      
              if (!pendingDocs.isEmpty()) {
                  Logger.log("There are " + pendingDocs.size() + " documents pending");
      
                  final String firstDoc = pendingDocs.iterator().next();
      
                  repl.addChangeListener(change -> {
                      Logger.log("Replicator activity level is " + change.getStatus().getActivityLevel());
                      try {
                          if (!repl.isDocumentPending(firstDoc, collection)) {
                              Logger.log("Doc ID " + firstDoc + " has been pushed");
                          }
                      }
                      catch (CouchbaseLiteException err) {
                          Logger.log("Failed getting pending docs", err);
                      }
                  });
      
                  repl.start();
                  this.thisReplicator = repl;
              }
          }
      
          public void databaseReplicatorExample(@NonNull Set<Collection> srcCollections, @NonNull Database targetDb) {
              // This is an Enterprise feature:
              // the code below will generate a compilation error
              // if it's compiled against CBL Android Community Edition.
              // Note: the target database must already contain the
              //       source collections or the replication will fail.
              final Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new DatabaseEndpoint(targetDb))
                      .addCollections(srcCollections, null)
                      .setType(ReplicatorType.PUSH));
      
              // Start the replicator
              // (be sure to hold a reference somewhere that will prevent it from being GCed)
              repl.start();
              thisReplicator = repl;
          }
      
          public void replicationWithCustomConflictResolverExample(Set<Collection> srcCollections, URI targetUri) {
              Replicator repl = new Replicator(
                  new ReplicatorConfiguration(new URLEndpoint(targetUri))
                      .addCollections(
                          srcCollections,
                          new CollectionConfiguration()
                              .setConflictResolver(new LocalWinConflictResolver())));
      
              // Start the replicator
              // (be sure to hold a reference somewhere that will prevent it from being GCed)
              repl.start();
              thisReplicator = repl;
          }
      }
      JSON String Format

      If your query selects ALL then the JSON format will be:

      {
        database-name: {
          key1: "value1",
          keyx: "valuex"
        }
      }

      If your query selects a sub-set of available properties then the JSON format will be:

      {
        key1: "value1",
        keyx: "valuex"
      }