Ktor server for beginners – MongoDB (Part 2 – queries)

1 Comment / Back End Tutorial, database, kotlin, ktor, tutorial / By

Level: Beginner

Video tutorial can be found here

Welcome back!, in the previous lesson we learned how to setup our mongoDB and how to add, read, update and delete documents from our collections, in this lesson we are going to learn how to make queries to filter and sort our documents!.

Sorting data

To sort the list of documents returned by MongoDB we can use ascendingSort and descendingSort and we pass what value we would like to use for sorting, for example here we do descendingSort on the name of the fruit.

  • Kotlin Driver (Official)
  • KMongo
suspend fun getFruits(): List<Fruit> {
    return fruits.find().sort(Sorts.descending(Fruit::name.name)).toList()
}
 
suspend fun getFruits(): List<Fruit> {
    return fruits.find().descendingSort(Fruit::name).toList()
}

to allow the client to specify how it wants the items to be sorted let’s introduce a variable to the function and let it determine how to sort items

  • Kotlin Driver (Official)
  • KMongo
DB - sorting
suspend fun getFruits(sortField: String = Fruit::name.name, sortDirection: Int = 1): List<Fruit> {
    return if (sortDirection < 0){
        fruitCollection.find().sort(Sorts.descending(sortField)).toList()
    } else {
        fruitCollection.find().sort(Sorts.ascending(sortField)).toList()
    }
}
 
DB - sorting
suspend fun getFruits(sortField: KProperty1<Fruit, String>, sortDirection: Int = 1): List<Fruit> {
    return if (sortDirection < 0) {
        fruits.find().descendingSort(sortField).toList()
    }else{
        fruits.find().ascendingSort(sortField).toList()
    }
}

above the sortField allows us to send what field we would like to sort by we give it a data type of KProperty1 with our data class Fruit and the data type witch is a string in our case, the sort direction is an integer we will use -1 for descending and 1 for ascending.

KProperty1 is a Kotlin class that represents a read-only property of a class or interface. It is often used to get or set the value of a property through reflection.

Note that KProperty1 is a generic class with two type parameters. The first type parameter is the type of the class that contains the property (Fruit in our example), and the second type parameter is the type of the property (String in our example).

lets modify our route to work with theses changes

  • Kotlin Driver (Official)
  • KMongo
Route -  sorting
get("/fruits/{sort_by?}/{sort_direction?}") {
            // read the sort_by from the parameters and assign it to sortBy variable
            val sortBy = when(call.parameters["sort_by"] ?: "name") {
                "name" -> Fruit::name.name
                "id" -> Fruit::id.name
                // if the parameter sent by client does not match any values from our model return a bad request
                else -> return@get call.respond(
                    HttpStatusCode.BadRequest,
                    SimpleResponse(success = false, message = "invalid parameter for sort_by")
                )
            }

            val sortDirection = when (call.parameters["sort_direction"] ?: "asc") {
                "dec" -> -1
                "asc" -> 1
                else -> return@get call.respond(
                    HttpStatusCode.BadRequest,
                    SimpleResponse(success = false, message = "invalid parameter for sort_direction")
                )
            }
            // get fruits from the database
            call.respond(HttpStatusCode.OK, getAllFruits(sortField = sortBy,sortDirection = sortDirection))
        }
 
Route -  sorting
    get("/fruits/{sort_by?}/{sort_direction?}") {
        // read the sort_by from the parameters and assign it to sortBy variable
        val sortBy = when(call.parameters["sort_by"] ?: "name") {
            "name" -> Fruit::name
            "id" -> Fruit::id
            // if the parameter sent by client does not match any values from our model return a bad request
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for sort_by")
            )
        }

        val sortDirection = when (call.parameters["sort_direction"] ?: "asc") {
            "dec" -> -1
            "asc" -> 1
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for sort_direction")
            )
        }
        // get fruits from the database
        call.respond(HttpStatusCode.OK, getFruits(sortField = sortBy,sortDirection = sortDirection))
    }

Now we can sort our results!

Filtering documents

To filter results we need to pass our filters to the find function, we can use Kmongo’s type safe queries to make this easier, but first let’s add a season field to our Fruit data class

  • Kotlin Driver (Official)
  • KMongo
Fruit data class
@Serializable
data class Fruit(
    @SerialName("_id")
    val id: String = BsonObjectId().toString(),
    val name: String,
    val season: Season = Season.Unknown,
    val countries: List<String> = emptyList(),
){
    enum class Season {
        Spring, Winter, Summer, Autumn, Unknown
    }
}
 
Fruit data class
@Serializable
data class Fruit(
    val name: String,
    val season: Season = Season.Unknown,
    val countries: List<String> = emptyList(),
    @BsonId
    val id: String = ObjectId().toString()
){
    enum class Season {
        Spring, Winter, Summer, Autumn, Unknown
    }
}

let’s add some summer fruits …

Now if we would like to return all summer fruits we can do that by passing the following to the find function, here the eq infix function will give a BSON filter that returns all season values that are equal to Season.Summer

In MongoDB, a BSON filter is a document that specifies criteria used to select documents from a collection. It is used to query the database and retrieve documents that match certain conditions.

fruits.find(Fruit::season eq Fruit.Season.Summer).toList()

lets modify our getFruits function to take in a season that we want to filter

  • Kotlin Driver (Official)
  • KMongo
DB - sort and season filter
suspend fun getFruits(
    sortField: String = Fruit::name.name,
    sortDirection: Int = 1,
    filter: Fruit.Season? = null
): List<Fruit> {
    val seasonFilter = if (filter != null) Filters.eq(Fruit::season.name, filter) else Filters.empty()
    return if (sortDirection < 0) {
        fruitCollection.find(seasonFilter).sort(Sorts.descending(sortField)).toList()
    } else {
        fruitCollection.find(seasonFilter).sort(Sorts.ascending(sortField)).toList()
    }
}
 
DB - sort and season filter
suspend fun getFruits(
    sortField: KProperty1<Fruit, String>,
    sortDirection: Int = 1,
    filter: Fruit.Season? = null
): List<Fruit> {
    // if season is null set it as EMPTY_BSON witch means we won't filter the results
    val seasonFilters = if (filter == null) EMPTY_BSON else Fruit::season eq filter
    return if (sortDirection < 0) {
        fruits.find(seasonFilters).descendingSort(sortField).toList()
    } else {
        fruits.find(seasonFilters).ascendingSort(sortField).toList()
    }
}

in our routes file we will need to receive the season parameter

Route- filter  season param
        val season = when (call.parameters["season"]) {
            "summer" -> Fruit.Season.Summer
            "winter" -> Fruit.Season.Winter
            "autumn" -> Fruit.Season.Autumn
            "spring" -> Fruit.Season.Spring
            null -> null
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for season")
            )
        }
// and pass the season to the getFruits function
call.respond(HttpStatusCode.OK, getFruits(sortField = sortBy, sortDirection = sortDirection, season = season)

Combining filters

we now need a way to filter results of a season and also of a country, for that we can use the and function.

  • Kotlin Driver (Official)
  • KMongo
DB - filter fruits  with 2 filters
suspend fun getFruits(
    sortField: String = Fruit::name.name,
    sortDirection: Int = 1,
    filter: Fruit.Season? = null,
    country: String? = null
): List<Fruit> {
    // if season is null set it as Filters.empty witch means we won't filter the results
    val seasonFilters = if (seasons.isNullOrEmpty()) Filters.empty() else Filters.`in`(Fruit::season.name, seasons)
    val countryFilter = if (countries.isNullOrEmpty()) Filters.empty() else Filters.`in`(Fruit::countries.name, countries)

    // this version of the regex function requires that we pass the field name as a string that's why we use .name on Fruit::name
    val searchQuery = if (query.isNullOrEmpty()) Filters.empty() else Filters.regex(Fruit::name.name,query,"i")
    return if (sortDirection < 0) {
        fruits.find(Filters.and(seasonFilters, countryFilter, searchQuery)).sort(Sorts.descending(sortField.name)).toList()
    } else {
        fruits.find(Filters.and(seasonFilters, countryFilter, searchQuery)).sort(Sorts.ascending(sortField.name)).toList()
    }
}
 
DB - filter fruits  with 2 filters
suspend fun getFruits(
    sortField: KProperty1<Fruit, String>,
    sortDirection: Int = 1,
    season: Fruit.Season? = null,
    country: String?
): List<Fruit> {
    // if season is null set it as EMPTY_BSON witch means we won't filter the results
    val seasonFilters = if (season == null) EMPTY_BSON else Fruit::season eq season
    // country filter
    val countryFilter = if (country == null) EMPTY_BSON else Fruit::countries contains country
    return if (sortDirection < 0) {
        // we use the and function to use two BSONs in the same time
        fruits.find(and(seasonFilters, countryFilter)).descendingSort(sortField).toList()
    } else {
        fruits.find(and(seasonFilters, countryFilter)).ascendingSort(sortField).toList()
    }
}

our route should now look something like this

Route - get fruits with multi filters
    get("/fruits/{sort_by?}/{sort_direction?}/{season?}/{country?}") {
        // read the sort_by from the parameters and assign it to sortBy variable
        val sortBy = when (call.parameters["sort_by"] ?: "name") {
            "name" -> Fruit::name
            "id" -> Fruit::id
            // if the parameter sent by client does not match any values from our model return a bad request
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for sort_by")
            )
        }

        val sortDirection = when (call.parameters["sort_direction"] ?: "asc") {
            "dec" -> -1
            "asc" -> 1
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for sort_direction")
            )
        }

        val season = when (call.parameters["season"]) {
            "summer" -> Fruit.Season.Summer
            "winter" -> Fruit.Season.Winter
            "autumn" -> Fruit.Season.Autumn
            "spring" -> Fruit.Season.Spring
            null -> null
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for season")
            )
        }
        val country = call.parameters["country"]

        // get fruits from the database
        call.respond(
            HttpStatusCode.OK,
            getFruits(sortField = sortBy, sortDirection = sortDirection, season = season, country = country)
        )
    }

Filtering by multiple seasons and countries

Now we can get the list of fruits for a specific season and a specific country, but sometimes we need to pass in multiple two or more seasons and get the results for any fruit from the the provided seasons, to do this we will need to pass a list of parameters we’ll get to how to do that in postman in a moment but first lets handle the list in our server, to receive a list we need to use parameters.getAll and then we will use forEach to loop on every item and add it to our Seasons list.

we’ll do the same for our list of countries expect that we won’t use forEach as we won’t need it because countries is a list of strings unlike the list of seasons.

Route - get fruits with multi values per parameter
    get("/fruits/{sort_by?}/{sort_direction?}/{season[]?}/{country[]?}") {
        // read the sort_by from the parameters and assign it to sortBy variable
        val sortBy = when (call.parameters["sort_by"] ?: "name") {
            "name" -> Fruit::name
            "id" -> Fruit::id
            // if the parameter sent by client does not match any values from our model return a bad request
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for sort_by")
            )
        }

        val sortDirection = when (call.parameters["sort_direction"] ?: "asc") {
            "dec" -> -1
            "asc" -> 1
            else -> return@get call.respond(
                HttpStatusCode.BadRequest,
                SimpleResponse(success = false, message = "invalid parameter for sort_direction")
            )
        }
        val seasons = mutableListOf<Fruit.Season>()
        call.parameters.getAll("season")?.forEach { name ->
            println("item name: $name")
            // add an item to the list of seasons
            seasons.add(
                when (name) {
                    "summer" -> Fruit.Season.Summer
                    "winter" -> Fruit.Season.Winter
                    "autumn" -> Fruit.Season.Autumn
                    "spring" -> Fruit.Season.Spring
                    else -> return@get call.respond(
                        HttpStatusCode.BadRequest,
                        SimpleResponse(success = false, message = "invalid parameter $name for season")
                    )
                }
            )
        }

        // list of countries
        val countries = call.parameters.getAll("country")

        // get fruits from the database
        call.respond(
            HttpStatusCode.OK,
            getFruits(sortField = sortBy, sortDirection = sortDirection, seasons = seasons, countries = countries)
        )
    }

now we need to modify the getFruits function, first we change the function to take a list of fruits and a list of strings, then we will change the check to also be an EMPTY_BSON if the list is empty last thing we will replace the contains keyword with ‘in’ this will check if any fruit matches with any value from the list of countries/seasons

  • Kotlin Driver (Official)
  • KMongo
DB - filter fruits with list of seasons and countries
suspend fun getFruits(
    sortField: String = Fruit::name.name,
    sortDirection: Int = 1,
    filter: List<Fruit.Season>? = null,
    country: List<String>? = null
): List<Fruit> {
    println("country: $country")
    val seasonFilter = if (filter.isNullOrEmpty())  Filters.empty() else Filters.`in`(Fruit::season.name, filter)
    val countryFilter = if (country.isNullOrEmpty()) Filters.empty() else Filters.`in`(Fruit::countries.name, (country))
    return if (sortDirection < 0) {
        fruitCollection.find(Filters.and(seasonFilter, countryFilter)).sort(Sorts.descending(sortField)).toList()
    } else {
        fruitCollection.find(Filters.and(seasonFilter, countryFilter)).sort(Sorts.ascending(sortField)).toList()
    }
}
 
DB - filter fruits with list of seasons and countries
suspend fun getFruits(
    sortField: KProperty1<Fruit, String>,
    sortDirection: Int = 1,
    seasons: List<Fruit.Season>? = null,
    countries: List<String>?
): List<Fruit> {
    // if season is null set it as EMPTY_BSON witch means we won't filter the results
    val seasonFilters = if (seasons.isNullOrEmpty()) EMPTY_BSON else Fruit::season `in` seasons
    val countryFilter = if (countries.isNullOrEmpty()) EMPTY_BSON else Fruit::countries `in` countries
    return if (sortDirection < 0) {
        fruits.find(and(seasonFilters, countryFilter)).descendingSort(sortField).toList()
    } else {
        fruits.find(and(seasonFilters, countryFilter)).ascendingSort(sortField).toList()
    }
}

Now lets try adding multiple seasons, we can do that in post man by adding the same parameter with different values as you can see in the screenshot below.

Searching the database

to search for a fruit in our legendary database we can’t use the eq function as that would return only fruits that match exactly the keyword, what we can use instead is the regex function.

to our getFruits function we add the following as a third argument to our and function: Fruit::name regex query where query is our search query 🙂

  • Kotlin Driver (Official)
  • KMongo
DB - search
suspend fun getFruits(
    sortField: String = Fruit::name.name,
    sortDirection: Int = 1,
    filter: List<Fruit.Season>? = null,
    country: List<String>? = null,
    query: String? = null,
): List<Fruit> {
    println("country: $country")
    val seasonFilter = if (filter.isNullOrEmpty()) Filters.empty() else Filters.`in`(Fruit::season.name, filter)
    val countryFilter = if (country.isNullOrEmpty()) Filters.empty() else Filters.`in`(Fruit::countries.name, (country))
    val queryFilter = if (query.isNullOrEmpty()) Filters.empty() else Filters.regex(Fruit::name.name, query)
    return if (sortDirection < 0) {
        fruitCollection.find(Filters.and(seasonFilter, countryFilter, queryFilter)).sort(Sorts.descending(sortField))
            .toList()
    } else {
        fruitCollection.find(Filters.and(seasonFilter, countryFilter, queryFilter)).sort(Sorts.ascending(sortField))
            .toList()
    }
}
 
DB - search
suspend fun getFruits(
    sortField: KProperty1<Fruit, String>,
    sortDirection: Int = 1,
    seasons: List<Fruit.Season>? = null,
    countries: List<String>?,
    query: String?
): List<Fruit> {
    // if season is null set it as EMPTY_BSON witch means we won't filter the results
    val seasonFilters = if (seasons.isNullOrEmpty()) EMPTY_BSON else Fruit::season `in` seasons
    val countryFilter = if (countries.isNullOrEmpty()) EMPTY_BSON else Fruit::countries `in` countries
    // search BSON 
    val searchQuery = if (query.isNullOrEmpty()) EMPTY_BSON else Fruit::name regex query
    return if (sortDirection < 0) {
        fruits.find(and(seasonFilters, countryFilter, searchQuery)).descendingSort(sortField).toList()
    } else {
        fruits.find(and(seasonFilters, countryFilter, searchQuery)).ascendingSort(sortField).toList()
    }
}

and off course we add the search query to our end point and pass it to the getFruits function

Route - pass search query
// search query
val query = call.parameters["query"]

// get fruits from the database
call.respond( HttpStatusCode.OK,getFruits(sortField = sortBy, sortDirection = sortDirection, seasons = seasons, countries = countries, query = query)

Now let’s try to search

I searched for “ap” but Apple didn’t come up in the results 🙁 !, this is because the search is case sensitive to make the search case insisitive we need to pass an operator to the regex function this operator is “i” we can pass it like this as a string or use the extension method provided by Kmongo and transform a Pattern from the java.util.regex to a string this way we won’t have to remember the operators as a string.

  • Kotlin Driver (Official)
  • KMongo
DB - make search case insensitve
val queryFilter = if (query.isNullOrEmpty()) Filters.empty() else Filters.regex(Fruit::name.name, query,"i")

DB - make search case insensitve
// 1st way    
// this veriosn of the regex function requires that we pass the field name as a string that's why we use .name on Fruit::name
    val searchQuery = if (query.isNullOrEmpty()) EMPTY_BSON else regex(Fruit::name.name,query,"i") 

// 2nd way
    val searchOperator = PatternUtil.getOptionsAsString(Pattern.compile("",Pattern.CASE_INSENSITIVE))
    // this veriosn of the regex function requires that we pass the field name as a string that's why we use .name on Fruit::name
    val searchQuery = if (query.isNullOrEmpty()) EMPTY_BSON else regex(Fruit::name.name,query,searchOperator)

Now we search again for “ap” and Apple shows up!

As always the source code is available on Github

Extra – Filters list

  • or – Creates a filter that preforms a logical OR of the provided list of filters. (contrary to and only one of the filters has to meet the condition)
  • ne – Creates a filter that matches all documents where the value of the field name does not equal the specified value.
  • lt – Creates a filter that matches all documents where the value of the given property is less than the specified value.
  • gt – Creates a filter that matches all documents where the value of the given property is greater than the specified value.
  • lte – Creates a filter that matches all documents where the value of the given property is less than or equal to the specified value.
  • gte – Creates a filter that matches all documents where the value of the given property is greater than or equal to the specified value.
  • nin – Creates a filter that matches all documents where the value of a property does not equal any of the specified values or does not exist.
5 1 vote
Article Rating
Subscribe
Notify of
guest


1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
trackback

[…] lesson 3 we took a look at how to save and perform different queries on our MongoDB now that we have decent […]

0
Reply