Query Result Sets
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 PropertiesMeta.id
— Metadata (such as the_id
) — see: Document ID OnlyFunction_.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 ofSelectResult
expressions provided in theSELECT
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.
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.
[
{
"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.
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.
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.
[
{
"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.
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.
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.
[
{
"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.
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
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.
{
"count": 6
}
Result Set Access
Access the count using its alias name ("count" in this example) — see Example 12.
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.
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
andtoPlainList
onResult
. - 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
// hotel.dart
part 'hotel.g.dart';
(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);
}
(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:
{
"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:
{
"key1": "value1",
"keyx": "valuex"
}