I can´t figure out how to use wildcards on a native query for MongoDB. Here is the query that I wish to use wildcards with:
[{"$match":{“nome_entidade”:“Alto Forno”}},{"$project":{“Ponto_A_456_1”:{ “$subtract”: [ “$Ponto_A_456_1”,860]},“nome_entidade”:"$nome_entidade"}},{"$group":{"_id":null,“max”:{"$max":"$Ponto_A_456_1"}}},{"$sort":{"_id":1}},{"$project":{"_id":false,“max”:true}}]
I need to match “nome.entidade” to all results that contain “Forno” on the string and not just the string itself. In SQL it would be something like “%Forno%”.
I tried to use many syntaxes that I´ve found googling, but none of them worked with Metabase.
Is it a Metabase limitation? If not, please advice me on how to achieve the result I need.
I´m using Metabase 0.33.4 and MongoDB 4.0.5.
Thank you!
P.S.: I´m using the native query builder because I need to perform a calculation that I also could’t find a way to do it using the custom query UI (no calculated fields for MongoDB)…
Finally I got to figure out how to write the question (using $regex):
[{"$match":{“nome_entidade”:{"$regex" : “alto”, “$options” : “i”}}},{"$project":{“Ponto_A_456_1”:{"$subtract": [ “$Ponto_A_456_1”,860]},“nome_entidade”:"$nome_entidade"}},{"$group":{"_id":null,“max”:{"$max":"$Ponto_A_456_1"}}},{"$sort":{"_id":1}},{"$project":{"_id":false,“max”:true}}]
Hope this will help someone else!