Where can we find the list of supported SQL functions in the new-ish Atlas SQL language? Things like SUBSTRING, MOD, date/time, etc. functions? There has to be a list of them, organized by data type, somewhere - I can’t find it though. (I found the list of aggregate functions, but not the list of built-ins for the various supported field types.)
Hello @dtabass
Within our online docs, we have a few places that give more info about our dialect: Mongosql:
If these 2 sources do not give you the information you are looking for please feel free to email me and I can provide you with a PDF I use to reference a bunch of different examples I have used over the last few years. email: alexi.antonino@mongodb.com
For Example, here is a code snippet that uses SubString, datetime, Flatten and Unwind using the MongoDB sample_supplies database and sales collection:
Select Cast(_id as string) as _id,
couponUsed, customer_age, customer_gender, customer_satisfaction, items_name, items_price as price, items_quantity,
(items_quantity * items_price) as totalPrice,
storeLocation, saleDate, purchaseMethod ,
cast(saleDate as Timestamp)as testDateTimestamp,
SUBSTRING (Cast(DATETRUNC(DAY,saleDate)as string),0,10) as DateOnly
from Flatten(Unwind(Supplies.Sales with Path=>Sales.items))
where items_quantity = 2
limit 100
Best to you,
Alexi