Does the “text” operator make the query look into non-text fields as well?
My requirement is for the search to return all documents that have the matching text (number/string/date/bool) in any field. Is this possible?
Thanks,
Prasad
Does the “text” operator make the query look into non-text fields as well?
My requirement is for the search to return all documents that have the matching text (number/string/date/bool) in any field. Is this possible?
Thanks,
Prasad
Hey @Prasad_Kini,
I tried to check this using the text
operator in Atlas(used dynamic indexing on sample_airbnb
dataset). For string fields, it worked as expected.
> db.listingsAndReviews.aggregate([{ $search: { index: 'default', text: { query: '1', path: 'minimum_nights' } } },
{$project:{"_id":0,"name":1,score:{$meta:"searchScore"}}}])
[
{ name: 'Double Room en-suite (307)', score: 0.49679940938949585 },
{ name: 'City center private room with bed', score: 0.49679940938949585 },
{ name: 'Friendly Apartment, 10m from Manly', score: 0.49679940938949585 },
{ name: 'Great studio opp. Narrabeen Lake', score: 0.49679940938949585 },
...
}
But when used for a numeric field, it didn’t return any documents and instead returned the following error.
sample_airbnb> db.listingsAndReviews.aggregate([{ $search: { index: 'default', text: { query: 1, path: 'bathrooms' } } },
{$project:{"_id":0,"name":1,score:{$meta:"searchScore"}}}])
MongoServerError: PlanExecutor error during aggregation :: caused by :: Remote error from mongot :: caused by :: "text.query" must be a string
As per the text operator documentation, the query value needs be a string or an array of strings.
You can, however, try using other Atlas Search operators based on your use case like using compound
with equals
for booleans and date fields. You can read more about these from the documentation:
Compound Search
Equals
Please feel free to reach out for anything else as well.
Regards,
Satyam
Hi @Satyam,
I haven’t been able to find any operator that would be able to do a “text” search on all the fields (string, number, boolean, date, arrays etc) in a document. Would you be able to help?
Thanks,
Prasad
Hey @Prasad_Kini,
As already pointed out
using just the text
operator to do a search for all the data types you mentioned is currently not supported. Hence, you may wish to consider using other operators like the compound
operator with equals
. equals
supports querying the date, numeric as well as boolean fields.
Regards,
Satyam
Hi @Satyam,
I do know that text operators cannot be used for my requirement by itself and that is the reason for this post.
Compound with equals will do an exact match. I need to support partial matches on any field.
e.g. Searching for 2022 should pickup all documents with any field containing 2022 including strings, dates and numbers. In the sample collection below, the query should bring back all the documents.
{
"projects": [
{
"name": "2021 Enhancements",
"release_date": ISODate("2022-07-21T00:00:00.000Z"),
"num_users": 364
},
{
"name": "2022 Enhancements",
"release_date": ISODate("2023-03-31T00:00:00.000Z"),
"num_users": 178
},
{
"name": "2023 Enhancements",
"release_date": ISODate("2023-06-30T00:00:00.000Z"),
"num_users": 12022
},
{
"name": "2024 Enhancements",
"release_date": ISODate("2024-06-30T00:00:00.000Z"),
"num_users": 2022
}
]
}
Thanks,
Prasad
Hey @Prasad_Kini,
Unfortunately, this won’t be possible by using one search operator, hence the suggestion was to use the text operator for strings and compound with equals for booleans, numbers, and dates.
If you want to match string patterns in non-string data types, you will have to explore other data modeling approaches. For example, Atlas text operator search will work if all your fields are strings and so if you can model your data accordingly, you might be able to use that.
Regards,
Satyam
Modeling the data in a way that won’t conform to the types that the data actually represents goes against basic data modeling principles. I am not sure how it would affect performance or other requirements. This is not an option for us at this time.
Having said that, could you please share a query depicting how to search for the number 2022 in the sample collection below using compound with equals?
{
"projects": [
{
"name": "2021 Enhancements",
"release_date": ISODate("2022-07-21T00:00:00.000Z"),
"num_users": 364
},
{
"name": "2022 Enhancements",
"release_date": ISODate("2023-03-31T00:00:00.000Z"),
"num_users": 178
},
{
"name": "2023 Enhancements",
"release_date": ISODate("2023-06-30T00:00:00.000Z"),
"num_users": 120226
},
{
"name": "2024 Enhancements",
"release_date": ISODate("2024-06-30T00:00:00.000Z"),
"num_users": 2022
}
]
}
Thanks,
Prasad