filter By mongo string id like (~)

[chlegou]

Hi there,

when i was testing you library, nice work btw, I was trying to filter entity ID by like '~' but wasn't working.

/filter?filter=id : '612f8b56800fb96a21e7dc23' // this work (filtering with ':')
/filter?filter=id ~ '612f8b56800fb96a21e7dc23' // this doesn't work (return empty list)
/filter?filter=id ~ '612f8b' // this doesn't work (return empty list)

logging the document value, this is how it's written (when using like '~' ):

doc: Document{{id=BsonRegularExpression{pattern='612f8b56800fb96a21e7dc23', options=''}}}

btw, here is my entity:

@Document(collection = "person")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder(toBuilder = true)
public class Person{

    @Id
    private String id ;
    
    ........
}

I have made some search, and i find out that i can't search by id portion, since it's saved as ObjectId, and not as string.
link: https://stackoverflow.com/a/32777342/4771750

... but do you know a way to do it?

[chlegou]

Digging more in the issue, i have found a way to do it: (tested and it's working)

db.getCollection('person').find({ 
    _id: {
        $gte: ObjectId("612a00000000000000000000"),
        $lt:    ObjectId("612affffffffffffffffffff"),
    } 
})

the above code filter id in a range of ids that starts with '612a'. ( id ~ '612a') or ( id > '612a')
credits: https://stackoverflow.com/a/17837198/4771750

could you please add a feature that converts: ( id ~ '612a') to the above example?

much appreciated!

Thanks! :)

[torshid]

Hi @chlegou 👋

It should work with /filter?filter=id : '%612%'. The ~ comparator actually uses the regex query operator.

[chlegou]

hi again,

I have made new tests, but didn't worked. i tried many things getting different results:

(Before, remember that my id is of type String in entity).

/?filter=id : '612f' // build : Document{{id=612f}}
/?filter=id : '%612f%' //400: Bad request
// i encoded '%' to '%25'  for url encode
/?filter=id : '%25612f%25' // build : Document{{id=%612f%}}

none on the working queries gave correct answer.

here is objectId list i have:

I'm working with version V1.0.5

waiting for your answer.

[torshid]

I'm sorry it should have been /filter?filter=id ~ '%612%', not : but ~.

[chlegou]

again, not working, here is the tests i made:

/?filter=id ~ '612f' // build : Document{{id=BsonRegularExpression{pattern='612f', options=''}}}
/?filter=id ~ '612f%' //400: Bad request
/?filter=id ~ '%612f%' //400: Bad request
// i encoded '%' to '%25'  for url encode
/?filter=id ~ '612f%25' // build : Document{{id=BsonRegularExpression{pattern='612f%', options=''}}}
/?filter=id ~ '%25612f%25' // build : Document{{id=BsonRegularExpression{pattern='%612f%', options=''}}}
// i changed '%' with  '*'  as mentioned is docs
/?filter=id ~ '612f*' // build : Document{{id=BsonRegularExpression{pattern='612f*', options=''}}}
/?filter=id ~ '*612f*' // Exception: "Query failed with error code 51091 and error message 'Regular expression is invalid: nothing to repeat' on server localhost:27017; nested exception is com.mongodb.MongoQueryException: Query failed with error code 51091 and error message 'Regular expression is invalid: nothing to repeat' on server localhost:27017"

I don't know if this info is important or not, here is how i'm passing the document to spring data repository:

//repository
@Query("?0")
Page<Entity> filter(Document document, Pageable pageable);

could you please provide me with working query for you as screenshot?

[torshid]

You were right, the problem has to do with the ObjectId. My queries were not working as expected.

I did some research and was able to get the thing done with the $where operator:

.find({ $where: "/612/.test(this._id)" })

It returns all the documents which have the string 612 inside the id string.

The problem is that this executes the JS code for each document from what I've read, it may not be always performant.

There is also another issue regarding the implementation of this feature: the field's real name is _id and not id as in the Java model. I don't see an easy and generic way for getting the field's real name from model field names.

A possible solution would be to let the user use the $where operator as he whishes, for example:

?filter= where('/612/.test(this._id)')

Not sure but it may lead to security issues.

Another possible solution would be to transparently use the $where operator instead of $regex when we detect that the field has the annotation @Id.

What do you think?

[chlegou]

A possible solution would be to let the user use the $where operator as he whishes,

?filter= where('/612/.test(this._id)')

I have tested to code you gave me, but didn't worked. it's returning 500 Internal Server Error. I think it's not available in Mongo yet.

com.turkraft.springfilter.exception.InvalidQueryException: Functions are not supported
    at com.turkraft.springfilter.generator.BsonGenerator.generate(BsonGenerator.java:39)
    at com.turkraft.springfilter.generator.BsonGenerator.generate(BsonGenerator.java:20)
    at com.turkraft.springfilter.generator.Generator.generate(Generator.java:36)
    at com.turkraft.springfilter.generator.Generator.generate(Generator.java:81)
    at com.turkraft.springfilter.generator.Generator.generate(Generator.java:24)
    at com.turkraft.springfilter.generator.BsonGenerator.run(BsonGenerator.java:25)
    at com.turkraft.springfilter.boot.BsonFilterArgumentResolver.getBson(BsonFilterArgumentResolver.java:61)
    at com.turkraft.springfilter.boot.BsonFilterArgumentResolver.resolveArgument(BsonFilterArgumentResolver.java:45)
    at org.springframework.web.method.support.HandlerMethodArgumentResolverComposite.resolveArgument(HandlerMethodArgumentResolverComposite.java:121)
    at org.springframework.web.method.support.InvocableHandlerMethod.getMethodArgumentValues(InvocableHandlerMethod.java:170)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1063)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)

Not sure but it may lead to security issues.

yeah who knows, as it may give full access to the collection.
I mostly recommend using the code i have found before:

Digging more in the issue, i have found a way to do it: (tested and it's working)

db.getCollection('person').find({ 
    _id: {
        $gte: ObjectId("612a00000000000000000000"),
        $lt:    ObjectId("612affffffffffffffffffff"),
    } 
})

the above code filter id in a range of ids that starts with '612a'. ( id ~ '612a') or ( id > '612a')
credits: https://stackoverflow.com/a/17837198/4771750

could you please add a feature that converts: ( id ~ '612a') to the above example?

$ObjectId is an Hex String of length 24: https://stackoverflow.com/a/25418489/4771750

knowing this, if the field has @id and with type String (Must check validity for Id with type BigInteger or Long! I think it's not an Objectid in that case) , we fill the input search string with paddings '00..00' and 'ff..ff' till having 24 length hex string. Then, following the search query, we build the document:

• ( id ~ '612a') will be:

   db.getCollection('person').find({ 
       _id: {
           $gte: ObjectId("612a00000000000000000000"),
           $lt:    ObjectId("612affffffffffffffffffff"),
       } 
   })
  

• ( id > '612a') will be:

   db.getCollection('person').find({ 
       _id: {
           $gte: ObjectId("612a00000000000000000000"),
       } 
   })
  

like this, we get the perfect solution like we want it without fearing of any security measures.

[torshid]

I went on with one of the solutions I proposed earlier, using gte and lte is not exactly equivalent to performing a like operation. You can always do it manually if needed.

You should finally be able to do the following with the new version https://github.com/turkraft/spring-filter/releases/tag/1.0.7:

/filter?filter=id ~ '612'

Which will produce:

Document{{$where=/612/.test(this._id)}}

Closing the issue for now. Feel free to reopen if it still does not work. :)

[chlegou]

It's working! 👍

thanks!