Query Result Sets

Description — How to use Couchbase Lite Query Result Sets

Related Content — SQL++ for Mobile | QueryBuilder | Indexes

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

The result set format and its handling varies slightly depending on the type of SelectResult expressions used. The result set formats you may encounter include those generated by:

SelectResult.all — see: All Properties
Expression.property — see: Specific Properties
Meta.id — Metadata (such as the _id) — see: Document ID Only
Function_.count — see: Select Count Only

To process the results of a query, you first need to execute it using Query.execute.

The result set of an aggregate query contains one result per aggregation group — see Select Count Only.
The result set of a query returning document properties contains zero or more results. Each result represents the data from a document that matched your search criteria (the WHERE clause). The composition of each result is determined by the combination of SelectResult expressions provided in the SELECT clause.

Select All Properties

Query

The SELECT clause for this type of query, which returns all document properties for each document matching the query criteria, is fairly straightforward — see Example 1.

Example 1. Query Selecting All Properties

final database = await Database.openAsync('travel-sample');
final query = const QueryBuilder()
  .select(SelectResult.all())
  .from(DataSource.database(database));

Result Set Format

The result set returned by queries using SelectResult.all contains dictionaries — one for each document matching the query criteria.

Each result contains a key-value pair, where the key is the database name and the value is a dictionary representing each document's properties — see: Example 2.

Example 2. Format of Result Set (All Properties)

[
  {
    "travel-sample": {
      "callsign": "MILE-AIR",
      "country": "United States",
      "iata": "Q5",
      "icao": "MLA",
      "id": 10,
      "name": "40-Mile Air",
      "type": "airline"
    }
  },
  {
    "travel-sample": {
      "callsign": "ALASKAN-AIR",
      "country": "United States",
      "iata": "AA",
      "icao": "AAA",
      "id": 10,
      "name": "Alaskan Airways",
      "type": "airline"
    }
  }
]

Result Set Access

In this case, access the retrieved document properties by looking up a dictionary in each result — as shown in Example 3.

Example 3. Using Document Properties (All)

final resultSet = await query.execute();
await for (final result in resultSet.asStream()) {
  final docProps = result.dictionary(0)!;

  final id = docProps.string('id');
  final name = docProps.string('name');
  final type = docProps.string('type');
  final city = docProps.string('city');
  print("$id $name $type $city");
}

Select Specific Properties

Query

Here we use Expression.property to specify the document properties we want our query to return — see: Example 4.

Example 4. Query Selecting Specific Properties

final database = await Database.openAsync('hotels');

final query = const QueryBuilder()
  .select(
    SelectResult.expression(Meta.id).as('docId'),
    SelectResult.expression(Expression.property('id')),
    SelectResult.expression(Expression.property('type')),
    SelectResult.expression(Expression.property('name')),
  )
  .from(DataSource.database(database));

Result Set Format

The result set returned when selecting only specific document properties contains dictionaries — one for each document matching the query criteria.

Each result comprises a key-value pair for each selected document property — see Example 5.

Example 5. Format of Result Set (Specific Properties)

[
  {
    "docId": "XjO9Ohk96F",
    "id": "hotel123",
    "type": "hotel",
    "name": "Hotel Ghia"
  },
  {
    "docId": "w2DCGPJ-0m",
    "id": "hotel456",
    "type": "hotel",
    "name": "Hotel Deluxe"
  }
]

Result Set Access

Access the retrieved properties by looking them up in each result — as shown in Example 6.

Example 6. Using Returned Document Properties (Specific Properties)

final resultSet = await query.execute();
await for (final result in await resultSet.asStream()) {
  final docId = result.string('docId')!;
  print('processing doc: $docId');

  final id = result.string('id')!;
  final hotel = Hotel(id);
  hotel.type = result.string('type')!;
  hotel.name = result.string('name')!;
  Do something with the hotel...
}

Select Document ID Only

Query

You would typically use this type of query if retrieval of document properties directly would consume excessive amounts of memory and-or processing time — see: Example 7.

Example 7. Query Selecting Only Document ID

final database = await Database.openAsync('hotels');
final query = QueryBuilder.createAsync()
  .select(SelectResult.expression(Meta.id))
  .from(DataSource.database(database));

Result Set Format

The result set returned by queries using a SelectResult expression of the form SelectResult.expression(Meta.id) contains dictionaries — one for each document matching the query criteria. Each result contains the ID under the id key — see Example 8.

Example 8. Format of Result Set (Document ID Only)

[
  {
    "id": "hotel123"
  },
  {
    "id": "hotel456"
  }
]

Result Set Access

In this case, access the properties of a document by unpacking the id and using it to get the document from the database — see: Example 9.

Example 9. Using Returned Document Properties (Document ID)

final resultSet = await query.execute();
await for (final result in resultSet.asStream()) {
  final docId = result.string('docId');
  print('processing doc: $docId');

  final doc = (await database.document(docId))!;
  final hotelId = doc.string('id');
  final name = doc.string('name');
  final city = doc.string('city');
  final type = doc.string('type');
  Do something with the variables...
}

Select Count Only

Query

Example 10. Query Selecting a Count Only

final database = await Database.openAsync('hotels');
final query = const QueryBuilder()
  .select(SelectResult.expression(Function_.count(Expression.all())).as('count'))
  .from(DataSource.database(database))
  .groupBy(Expression.property('type'));

Result Set Format

The result set returned by a count such as SelectResult.expression(Function_.count(Expression.all))).as('count') contains dictionaries with a key-value pair. The key is the count name, as defined using SelectResultAs.as — see: Example 11 for the format and Example 10 for the query.

Example 11. Format of Result Set (Count)

{
  "count": 6
}

Result Set Access

Access the count using its alias name ("count" in this example) — see Example 12.

Example 12. Using Returned Aggregate Value (Count)

final resultSet = await query.execute();
final results = await resultSet.allResults();
final result = results.first;
final count = result.integer('count');
print("There are $count documents.");

Handling Pagination

One way to handle pagination in high-volume queries is to retrieve the results in batches. Use the LIMIT and OFFSET clauses to return a defined number of results starting from a given offset — see: Example 13.

Example 13. Query Pagination

const offset = 0;
const limit = 20;

final database = await Database.openAsync('hotels');
final query = const QueryBuilder()
 .select(SelectResult.all())
 .from(DataSource.database(database))
 .limit(
    Expression.integer(limit),
    offset: Expression.integer(offset),
  );

JSON Result Sets

Couchbase Lite for Dart provides a convenience API to convert query results to:

Plain Dart objects through toPlainMap and toPlainList on Result.
JSON strings through Result.toJson.

Convert Result to Model

In Dart/Flutter you can use code generators to auto generate the code for handing serialization. See Creating model classes the json serializable way for more information. The example below shows how this can be done using the class Hotel with methods generated from the code generators. Also note when updating your model classes you will be required to run this command from the terminal to update your code generated classes:

flutter pub run build_runner build --delete-conflicting-outputs

Example 14. Convert Result to Model

// hotel.dart

part 'hotel.g.dart';

@JsonSerializable(explicitToJson: true)
class Hotel {
  const Hotel({
    required this.id,
    required this.name,
    required this.city,
    required this.country,
    this.description,
  });

  String id;
  String type;
  String name;
  String city;
  String country;
  String? description;

  factory Hotel.fromJson(Map<String, Object?> json) => _$HotelFromJson(json);

  Map<String, Object?> toJson() => _$HotelToJson(this);
}

@JsonSerializable(explicitToJson: true)
class HotelDao {
 const HotelDao(this.hotel);

 final Hotel hotel;

 factory HotelDao.fromJson(Map<String, Object?> json) =>
     _$HotelDaoFromJson(json);

  Map<String, Object?> toJson() => _$HotelDaoToJson(this);
}

final database = await Database.openAsync('hotels');
final query = const QueryBuilder()
 .select(SelectResult.all().as('hotel'))
 .from(DataSource.database(database));

final resultSet = await query.execute();

await for (final result in resultSet.asStream()) {
  final map = result.toPlainMap();
  final hotelDao = HotelDao.fromJson(map);
  final hotel = hotelDao.hotel;
  // Do something with the hotel...
}

JSON String Format

If your query selects all properties then the JSON format returned by Result.toJson will be:

Example 15. JSON Format for Select All

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

If your query selects a sub-set of available properties then the JSON format returned by Result.toJson will be:

Example 16. JSON Format for Select Specific Properties

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

Query Result Sets

Select All Properties​

Query​

Result Set Format​

Result Set Access​

Select Specific Properties​

Query​

Result Set Format​

Result Set Access​

Select Document ID Only​

Query​

Result Set Format​

Result Set Access​

Select Count Only​

Query​

Result Set Format​

Result Set Access​

Handling Pagination​

JSON Result Sets​

Convert Result to Model​

JSON String Format​

Select All Properties

Query

Result Set Format

Result Set Access

Select Specific Properties

Query

Result Set Format

Result Set Access

Select Document ID Only

Query

Result Set Format

Result Set Access

Select Count Only

Query

Result Set Format

Result Set Access

Handling Pagination

JSON Result Sets

Convert Result to Model

JSON String Format