Query Result Sets
How to use Couchbase Lite Query’s Result Sets
Query Execution
The execution of a Couchbase Lite 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
Return All Document Properties | Return Document ID Only | Return Specific Properties Only
The types of SelectResult formats you may encounter include those generated by :
QueryBuilder.select(SelectResult.all())— Using AllQueryBuilder.select(SelectResult.expression(Meta.id))— Using Doc ID Metadata such as the_idQueryBuilder.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": {
"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"
}
}
]
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.
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
Processing Results
Access Document Properties — All Properties | Access Document Properties — ID | Access Document Properties — Selected Properties
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: JSON Result Sets
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
val hotels = mutableMapOf<String, Hotel>()
query.execute().use { rs ->
rs.allResults().forEach {
// get the k-v pairs from the 'hotel' key's value into a dictionary
val docProps = it.getDictionary(0)
val docId = docProps!!.getString("id")
val docType = docProps.getString("type")
val docName = docProps.getString("name")
val docCity = docProps.getString("city")
// Alternatively, access results value dictionary directly
val id = it.getDictionary(0)?.getString("id")
hotels[id] = Hotel(
id,
it.getDictionary(0)?.getString("type"),
it.getDictionary(0)?.getString("name"),
it.getDictionary(0)?.getString("city"),
it.getDictionary(0)?.getString("country"),
it.getDictionary(0)?.getString("description")
)
}
}
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
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 Example 6.
Example 6. Access Selected Properties
JSON Result Sets
Use Result.toJSON() to transform your result into a JSON
string, which can easily be serialized or used as required in your application. See Example 7 for a
working example using kotlinx-serialization.
Example 7. Using JSON Results
// Uses kotlinx-serialization JSON processor
@Serializable
data class Hotel(val id: String, val type: String, val name: String)
val hotels = mutableListOf<Hotel>()
val query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("type"),
SelectResult.property("name")
)
.from(DataSource.collection(collection))
query.execute().use { rs ->
rs.forEach {
// Get result as JSON string
val json = it.toJSON()
// Get JsonObject map from JSON string
val mapFromJsonString = Json.decodeFromString<JsonObject>(json)
// Use created JsonObject map
val hotelId = mapFromJsonString["id"].toString()
val hotelType = mapFromJsonString["type"].toString()
val hotelName = mapFromJsonString["name"].toString()
// Get custom object from JSON string
val hotel = Json.decodeFromString<Hotel>(json)
hotels.add(hotel)
}
}
JSON String Format
If your query selects ALL then the JSON format will be:
If your query selects a sub-set of available properties then the JSON format will be: