CODEWITHSUNDEEP
springmongodbkotlin
Sachin
Sachin

Reputation: 527

Search on MongoDB across concatenated keys in same collection (e.g. firstname + lastname)

I'm trying to search MongoDB from Kotlin, across two keys: firstname + lastname concatenated together. I'm starting with just being able to display all documents in the collection, with the additional field, fullname, created, so I can then search on this field.

 val aggregation = if (searchKeyList.isNotEmpty()) {
                val concatExpression = "concat(#personalInformation.firstname, ', ', #personalInformation.lastname)"
                val aggregationOperation = Aggregation.project()
                    .andExpression(concatExpression).`as`("fullname")

                newAggregation(UserEntity::class.java, aggregationOperation)

This returns only the firstname field of the document, even then the field is strangely empty?

{
        "_id": "65900728fea9bea9cf327dd1",
        "personalInformation": {
            "firstname": ""
        }
    },
    {
        "_id": "65900734fea9bea9cf327dd2",
        "personalInformation": {
            "firstname": ""
        }
    },

Problem is, I can't even create a concatenated field, along with the entire original document. Is this even possible with MongoDB?

UPDATE

Following Andrew's help, this works in one DB hit, As the last step, I do remove the concatenated field (as I used it temporarily only, for the search)

I am intrigued though by how Andrew said MongoDB is not great at this for 5000 documents, and how ElasticSearch or OpenSearch might be better.

Q. Are there any guides on how to set this up alongside MongoDB, for performant querying (if the below is going to be slow on 5000 documents)?

 // build a dynamic query using the provided keys and concatenated values
        val typedAggregation = if (searchKeyList.isNotEmpty()) {
            val concatExpression = "concat(personalInformation.firstname, ' ', personalInformation.lastname)"
            Aggregation.newAggregation(
                Aggregation.addFields()
                    .addField("fullname")
                    .withValueOfExpression(concatExpression)
                    .build(),
                Aggregation.match(Criteria.where("fullname").regex(searchValue ?: "", "i")),
                Aggregation.project().andExclude("fullname")
            )
        } else {
            // return an empty flow if concatenatedFieldCriteria is empty
            return flowOf()
        }

Note: I've hardcoded the two strings, for clarity. They are meant to be passed through dynamically via the variable, searchKeyList, (so you can concatenate any 'n' number of fields)

Upvotes: 0

Views: 113

Answers (1)

AndrewL
AndrewL

Reputation: 3455

I don't know quite what is wrong with your code, but below is my working code.

We search the DB in two hits, first with the name search using an aggregation very like your code, and find a collection of ids. Then we pass those ids into a Criteria and query the database again adding other criteria that the application wants to search by.

val query = KQuery()

if (name != null && name.isNotEmpty()) {
    val nameSearchConcat = Aggregation.project()
        .andExpression("concat(givenName, ' ',familyName, '.', knownAs, ' ', familyName)")
        .`as`("nameSearch")

    val match = Aggregation.match(Criteria.where("nameSearch").regex(name, "i"))
    val newAggregation = Aggregation.newAggregation(nameSearchConcat, match)
    val aggregationResults =
        mongoTemplate.aggregate(newAggregation, "profile", ProfileNameSearchModel::class.java)
    val filteredProfileIds = aggregationResults.mappedResults.map { it.id }
    query.addCriteria(Criteria.where("_id").`in`(filteredProfileIds))
}

//other conditions here

val results = mongoTemplate.find(query.with(pageable), Profile::class.java)

This is not a performant search since weare not using proper text searching with a specialised index, so the Regex essentially does a full Collection scan - however this works well enoughfor 5000 thousand documents. (We're replacing this feature with a proper free text Search Engine).

Aside from the performance issues with this approach, it is functionally limited. Not only does the system not do automatic stemming of names, but suffers no match if parts of the names are not fully entered. You can see we have two alternate name fomulations (givenName familyName and knownAs familyName), but imagine you don't have a knownAs or nickname, if you have data like this:

givenName familyName
Alexander Bell

then this search approach

  • matches Alex
  • matches alexander
  • matches alexander bell
  • matches Bell
  • does not match Alex Bell
  • does not match A Bell

Upvotes: 0

Related Questions