Querying your Data
Description — Using Queries with Couchbase Lite on Swift
Related Content — Predictive Query | Live Query | Indexing
Query Format
Couchbase Lite for Swift’s database queries are based on expressions, of the form:
SELECT ____ FROM database WHERE ____, ORDERed by ____
Semantics are based on Couchbase’s N1QL query language.
There are several parts to specifying a query:
- SELECT
-
Specifies the projection, which is the part of the document that is to be returned.
- FROM
-
Specifies the database to query the documents from.
- JOIN
-
Specifies the matching criteria in which to join multiple documents.
- WHERE
-
Specifies the query criteria that the result must satisfy.
- GROUP BY
-
Specifies the query criteria to group rows by.
- ORDER BY
-
Specifies the query criteria to sort the rows in the result.
Indexing
Before we begin querying documents, let’s briefly mention the importance of having a query index. A query can only be fast if there’s a pre-existing database index it can search to narrow down the set of documents to examine — see also: Query Troubleshooting .
The following example creates a new index for the type
and name
properties.
{
"_id": "hotel123",
"type": "hotel",
"name": "Apple Droid"
}
let index = IndexBuilder.valueIndex(items:
ValueIndexItem.expression(Expression.property("type")),
ValueIndexItem.expression(Expression.property("name")))
try database.createIndex(index, withName: "TypeNameIndex")
If there are multiple expressions, the first one will be the primary key, the second the secondary key, etc.
Every index has to be updated whenever a document is updated, so too many indexes can hurt performance. Thus, good performance depends on designing and creating the right indexes to go along with your queries. |
SELECT statement
With the SELECT statement, you can query and manipulate JSON data. With projections, you retrieve just the fields that you need and not the entire document.
A SelectResult represents a single return value of the query statement.
You can specify a comma separated list of SelectResult
expressions in the select statement of your query.
For instance the following select statement queries for the document _id
as well as the type
and name
properties of all documents in the database.
In the query result, we print the _id
and name
properties of each row using the property name getter method.
{
"_id": "hotel123",
"type": "hotel",
"name": "Apple Droid"
}
let query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("type"),
SelectResult.property("name")
)
.from(DataSource.database(database))
do {
for result in try query.execute() {
print("document id :: \(result.string(forKey: "id")!)")
print("document name :: \(result.string(forKey: "name")!)")
}
} catch {
print(error)
}
The SelectResult.all()
method can be used to query all the properties of a document.
In this case, the document in the result is embedded in a dictionary where the key is the database name.
The following snippet shows the same query using SelectResult.all()
and the result in JSON.
let query = QueryBuilder
.select(SelectResult.all())
.from(DataSource.database(database))
[
{
"travel-sample": {
"callsign": "MILE-AIR",
"country": "United States",
"iata": "Q5",
"icao": "MLA",
"id": 10,
"name": "40-Mile Air",
"type": "airline"
}
},
{
"travel-sample": {
"callsign": "TXW",
"country": "United States",
"iata": "TQ",
"icao": "TXW",
"id": 10123,
"name": "Texas Wings",
"type": "airline"
}
}
]
WHERE statement
Similar to SQL, you can use the where clause to filter the documents to be returned as part of the query.
The select statement takes in an Expression
.
You can chain any number of Expressions in order to implement sophisticated filtering capabilities.
Comparison
The Expression Comparators can be used in the WHERE statement to specify on which property to match documents.
In the example below, we use the equalTo
operator to query documents where the type
property equals "hotel".
{
"_id": "hotel123",
"type": "hotel",
"name": "Apple Droid"
}
let query = QueryBuilder
.select(SelectResult.all())
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string("hotel")))
.limit(Expression.int(10))
do {
for result in try query.execute() {
if let dict = result.dictionary(forKey: "travel-sample") {
print("document name :: \(dict.string(forKey: "name")!)")
}
}
} catch {
print(error)
}
Collection Operators
Array Collection Operators are useful to check if a given value is present in an array.
CONTAINS Operator
The following example uses the ArrayFunction
to find documents where the public_likes
array property contains a value equal to "Armani Langworth".
{
"_id": "hotel123",
"name": "Apple Droid",
"public_likes": ["Armani Langworth", "Elfrieda Gutkowski", "Maureen Ruecker"]
}
let query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("name"),
SelectResult.property("public_likes")
)
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string("hotel"))
.and(ArrayFunction.contains(Expression.property("public_likes"), value: Expression.string("Armani Langworth")))
)
do {
for result in try query.execute() {
print("public_likes :: \(result.array(forKey: "public_likes")!.toArray())")
}
}
IN Operator
The IN
operator is useful when you need to explicitly list out the values to test against.
The following example looks for documents whose first
, last
or username
property value equals "Armani".
let values = [Expression.property("first"), Expression.property("last"), Expression.property("username")]
QueryBuilder
.select(SelectResult.all())
.from(DataSource.database(database))
.where(Expression.string("Armani").in(values))
Like Operator
The {http://docs.couchbase.com/mobile/2.8.0/couchbase-lite-swift/Protocols/ExpressionProtocol.html#/#/s:18CouchbaseLiteSwift18ExpressionProtocolP4likeyAaB_pAaB_pF[like(_:)] operator can be used for string matching.
The like
operator performs case sensitive matches.
So if you want to make the string matching case insensitive, you would have to use Function.lower
or Function.upper
to transform the matched string to lowercase or uppercase equivalents.
In the example below, we are looking for documents of type landmark
where the name property exactly matches the string "Royal engineers museum".
Note that since like
does a case sensitive match, we use Function.lower
to transform the matched string to the lowercase equivalent.
So the following query will return "landmark" type documents with the name matching "Royal Engineers Museum", "royal engineers museum", "ROYAL ENGINEERS MUSEUM" and so on.
let query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("country"),
SelectResult.property("name")
)
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string("landmark"))
.and(Function.lower(Expression.property("name")).like(Expression.string("Royal engineers museum")))
)
.limit(Expression.int(10))
do {
for result in try query.execute() {
print("name property :: \(result.string(forKey: "name")!)")
}
}
Wildcard Match
We can use %
sign within a like
expression to do a wildcard match against zero or more characters.
Using wildcards allows you to have some fuzziness in your search string.
In the example below, we are looking for documents of type
"landmark" where the name property matches any string that begins with "eng" followed by zero or more characters, the letter "e", followed by zero or more characters.
Once again, we are using Function.lower
to make the search case insensitive.
The following query will return "landmark" type
documents with name matching "Engineers", "engine", "english egg" , "England Eagle" and so on.
Notice that the matches may span word boundaries.
let query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("country"),
SelectResult.property("name")
)
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string("landmark"))
.and(Function.lower(Expression.property("name")).like(Expression.string("eng%e%")))
)
.limit(Expression.int(10))
Wildcard Character Match
We can use an _
sign within a like expression to do a wildcard match against a single character.
In the example below, we are looking for documents of type "landmark" where the name
property matches any string that begins with "eng" followed by exactly 4 wildcard characters and ending in the letter "r".
The following query will return "landmark" type
documents with the name
matching "Engineer", "engineer" and so on.
let query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("country"),
SelectResult.property("name")
)
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string("landmark"))
.and(Expression.property("name").like(Expression.string("eng____r")))
)
.limit(Expression.int(10))
Regex Operator
Similar to wildcard like
expressions, regex
expressions based pattern matching allow you to have some fuzziness in your search string.
The regex
operator is case sensitive.
For more on the regex spec used by Couchbase Lite see cplusplus regex reference page |
In the example below, we are looking for documents of type
"landmark" where the name property matches any string (on word boundaries) that begins with "eng" followed by exactly 4 wildcard characters and ending in the letter "r".
The following query will return "landmark" type documents with name matching "Engine", "engine" and so on.
Note that the \b
specifies that the match must occur on word boundaries.
let query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("name")
)
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string("landmark"))
.and(Expression.property("name").regex(Expression.string("\\bEng.*e\\b")))
)
.limit(Expression.int(10))
Deleted Document
You can query documents that have been deleted (tombstones) [1].
The following example shows how to query deleted documents in the database.
// Query documents that have been deleted
let query = QueryBuilder
.select(SelectResult.expression(Meta.id))
.from(DataSource.database(db))
.where(Meta.isDeleted)
JOIN statement
The JOIN clause enables you to create new input objects by combining two or more source objects.
The following example uses a JOIN clause to find the airline details which have routes that start from RIX.
This example JOINS the document of type "route" with documents of type "airline" using the document ID (_id
) on the "airline" document and airlineid
on the "route" document.
let query = QueryBuilder
.select(
SelectResult.expression(Expression.property("name").from("airline")),
SelectResult.expression(Expression.property("callsign").from("airline")),
SelectResult.expression(Expression.property("destinationairport").from("route")),
SelectResult.expression(Expression.property("stops").from("route")),
SelectResult.expression(Expression.property("airline").from("route"))
)
.from(
DataSource.database(database!).as("airline")
)
.join(
Join.join(DataSource.database(database!).as("route"))
.on(
Meta.id.from("airline")
.equalTo(Expression.property("airlineid").from("route"))
)
)
.where(
Expression.property("type").from("route").equalTo(Expression.string("route"))
.and(Expression.property("type").from("airline").equalTo(Expression.string("airline")))
.and(Expression.property("sourceairport").from("route").equalTo(Expression.string("RIX")))
)
GROUP BY statement
You can perform further processing on the data in your result set before the final projection is generated. The following example looks for the number of airports at an altitude of 300 ft or higher and groups the results by country and timezone.
{
"_id": "airport123",
"type": "airport",
"country": "United States",
"geo": { "alt": 456 },
"tz": "America/Anchorage"
}
let query = QueryBuilder
.select(
SelectResult.expression(Function.count(Expression.all())),
SelectResult.property("country"),
SelectResult.property("tz"))
.from(DataSource.database(database))
.where(
Expression.property("type").equalTo(Expression.string("airport"))
.and(Expression.property("geo.alt").greaterThanOrEqualTo(Expression.int(300)))
).groupBy(
Expression.property("country"),
Expression.property("tz")
)
do {
for result in try query.execute() {
print("There are \(result.int(forKey: "$1")) airports on the \(result.string(forKey: "tz")!) timezone located in \(result.string(forKey: "country")!) and above 300 ft")
}
}
There are 138 airports on the Europe/Paris timezone located in France and above 300 ft
There are 29 airports on the Europe/London timezone located in United Kingdom and above 300 ft
There are 50 airports on the America/Anchorage timezone located in United States and above 300 ft
There are 279 airports on the America/Chicago timezone located in United States and above 300 ft
There are 123 airports on the America/Denver timezone located in United States and above 300 ft
ORDER BY statement
It is possible to sort the results of a query based on a given expression result. The example below returns documents of type equal to "hotel" sorted in ascending order by the value of the title property.
let query = QueryBuilder
.select(
SelectResult.expression(Meta.id),
SelectResult.property("title"))
.from(DataSource.database(database))
.where(Expression.property("type").equalTo(Expression.string("hotel")))
.orderBy(Ordering.property("title").ascending())
.limit(Expression.int(10))
Aberdyfi
Achiltibuie
Altrincham
Ambleside
Annan
Ardèche
Armagh
Avignon
Date/Time Functions
Couchbase Lite documents support a date type that internally stores dates in ISO 8601 with the GMT/UTC timezone.
Couchbase Lite 2.5 adds the ability to run date comparisons in your Couchbase Lite queries. To do so, four functions have been added to the Query Builder API:
Function.StringToMillis(Expression.Property("date_time"))
-
The input to this will be a validly formatted ISO 8601
date_time
string. The end result will be an expression (with a numeric content) that can be further input into the query builder. Function.StringToUTC(Expression.Property("date_time"))
-
The input to this will be a validly formatted ISO 8601
date_time
string. The end result will be an expression (with string content) that can be further input into the query builder. Function.MillisToString(Expression.Property("date_time"))
-
The input for this is a numeric value representing milliseconds since the Unix epoch. The end result will be an expression (with string content representing the date and time as an ISO 8601 string in the device’s timezone) that can be further input into the query builder.
Function.MillisToUTC(Expression.Property("date_time"))
-
The input for this is a numeric value representing milliseconds since the Unix epoch. The end result will be an expression (with string content representing the date and time as a UTC ISO 8601 string) that can be further input into the query builder.