Hello. My company recently switched from Workbench and SQL database to mongoDB and metabase. We also have our old sql database in metabase.
Whenever I want to write a query, even the most basic one, I get the error message:
,JSON reader was expecting a value but found ‘select’." for the simplest query being:
select * from order
I then realized that maybe I need to use native, mongoDB syntax. I typed in:
db.inventory.find( {} ) and I also tried: db.collection.find() and I get the error:
JSON reader was expecting a value but found ‘db’.
I have no idea what I’m supposed to write in order to extract anything from my database. Can someone please help me?
SQL queries work just fine when I use SQL database in metabase.
Thank you for the answer, you really helped me out. However, the syntax turned out to be unintuitive and weird. To be hones I've never seen anything like this before. Here's the example of selecting two columns (order_id, created_at), using native query:
[{"$project":{"incrementId":"$incrementId", "createdAt~~~default":{"$let":{"vars":{"column":"$createdAt"},"in":"$$column"}} {"$limit":1048576}]
I'm not familiar with this way of querying at all. Is there any way to learn more about this? Is there a way to use SQL language instead of this to get the same outcome?
There might be something wrong with our dev team integrating mongo with metabase but I'm not sure if that's even the case. I know that I'm asking for much but this makes me unable to work on daily basis and I can't find answers elsewhere. I would appreciate any advice on that matter.
@marcino92 Metabase currently supports Native syntax for MongoDB. There's a lot of optimizations of the generated query in the upcoming 0.39.0, so something like your example would just become:
[{"$project":{"incrementId":"$incrementId","createdAt":"$createdAt"}},{"$limit":1048576}]
1 Like
Thank you, your syntax is working fine and it looks way better than mine. I look forward to 0.39.0 and optimazations. I would like to ask one last thing in this topic regarding your query:
[{"$project":{"incrementId":"$incrementId","createdAt":"$createdAt"}},{"$limit":1048576}]
How do I pick a specific date in createdAt in order to filter results?
@marcino92 I don't use Mongo, but I'll guess you would use $match - depending on which type of field it is and if you're having fun with timezones.
{"$match":{"$and":[{"$expr":{"$gte":["$createdAt",{"$dateFromString":{"dateString":"2020-12-11T23:00Z"}}]}},{"$expr":{"$lt":["$createdAt",{"$dateFromString":{"dateString":"2020-12-12T23:00Z"}}]}}]}}
But this is not specific to Metabase - it's just MongoDB Native queries, so you'll likely find much better resources on a forum dedicated to that or stackoverflow.com
1 Like
Thanks a lot, I owe you one.