Use like in MongoDB
I'm trying to write a query in mongo previously I have written in MySQL I'm trying to implement a search on multiple fields using the query
collection = get_connection(settings.DATABASE_NAME, settings.FARM_COLLECTION)
result = list(collection.find(
{'$or': [{'farm_name': "Test_Farm_26"}]}
).limit(filter['limit']).skip(filter['skip']))
print(result)
then I am getting this output
[{'_id': ObjectId('6331920021a8c44f7e95ea4e'), 'farm_id': 1, 'farm_acreage': 50, 'farm_name': 'Test_Farm_26', 'assignee_id': 1, 'is_active': True, 'farm_location': {'lgd_state_id': 9, 'district_code': 119, 'sub_district_code': 755, 'village_code': 122028, 'lat': 27.934293908219843, 'lng': 78.0054819610702}, 'area_latest_id': 1, 'zone_latest_id': 0, 'created_at': datetime.datetime(2022, 9, 26, 11, 50, 24, 138000), 'updated_at': datetime.datetime(2022, 9, 26, 11, 59, 50, 289000), 'created_by_id': 0, 'updated_by_id': 0, 'farm_area': [{'area_id': 1, 'area_acerage': 3, 'area_structure_type': 'polyhouse'}], 'farm_area_count': 1}]
as you see that the column name "farm_name" search filter is working when I am passing its total value but when I am giving its half value and trying to use the LIKE statement in MongoDB
I'm getting a blank list as an output
collection = get_connection(settings.DATABASE_NAME, settings.FARM_COLLECTION)
result = list(collection.find(
{'$or': [{'farm_name': "/26/"}]}
).limit(filter['limit']).skip(filter['skip']))
print(result)
Based on the comments and the edit, it looks like you are pretty close at this point after moving from an exact string match to a regex approach.
Your remaining issue is that the string test_farm_26
is not matching the regex /^26/i
. This has nothing to do with MongoDB directly. Rather, this is directly related to the definition of the regex. You have prepended the ^
character to the regex and that character matches the start of the string. So your regex will only match a string that starts with 26
. The string test_farm_26
does not start with that hence it is not matching. You can see that demonstrated in this example on regex101.
Resolving this requires simply changing your regex. Perhaps the most straightforward is just to drop the ^
character and make your regex /26/
instead (as demonstrated in this example). If instead you wanted the strings to end with 26
then the regex should be /26$
. So your desired query might look something like this:
db.collection.find({
farm_name: {
"$regex": "26$",
"$options": "i"
}
})
You can see a demonstration of that query in this mongoplayground example.