The cmdlet maps SQL queries into the corresponding Azure Cosmos DB SQL API queries. A detailed description of all the transformations is out of scope, but we will describe some of the common elements that are used. The cmdlet takes advantage of SQL API features such as the aggregation framework to compute the desired results.
SELECT Queries
Since all requests can be submitted to a specific collection, we can send any constant string as table name to the API. Following the Azure Portal standard we are using the "C" character as table name.
| |
| SQL Query | Sql API Query |
SELECT id, name FROM Users |
SELECT C.id, C.name FROM C |
SELECT * FROM Users WHERE name = 'A' |
SELECT * FROM C WHERE C.name = 'A' |
SELECT * FROM Users WHERE name = 'A' OR email = '[email protected]' |
SELECT * FROM C WHERE C.name = 'A' OR C.email = '[email protected]' |
SELECT id, grantamt FROM WorldBank WHERE grantamt IN (4500000, 85400000) OR grantamt = 16200000 |
SELECT C.id, C.grantamt FROM C WHERE C.grantamt IN (4500000, 85400000) OR C.grantamt = 16200000 |
SELECT * FROM WorldBank WHERE CountryCode = 'A' ORDER BY TotalCommAmt ASC |
SELECT * FROM C WHERE C.countrycode = 'AL' ORDER BY C.totalcommamt ASC |
SELECT * FROM WorldBank WHERE CountryCode = 'A' ORDER BY TotalCommAmt DESC |
SELECT * FROM C WHERE C.countrycode = 'AL' ORDER BY C.totalcommamt DESC |
Aggregate Queries
The cmdlet makes extensive use of this for various aggregate queries. See some examples below:
| |
| SQL Query | Sql API Query |
SELECT COUNT(grantamt) AS COUNT_GRAMT FROM WorldBank |
SELECT COUNT(C.grantamt) AS COUNT_GRAMT FROM C |
SELECT SUM(grantamt) AS SUM_GRAMT FROM WorldBank |
SELECT SUM(C.grantamt) AS SUM_GRAMT FROM C |
Built-In functions
| |
| SQL Query | Sql API Query |
SELECT IS_NUMBER(grantamt) AS ISN_ATTR, IS_NUMBER(id) AS ISN_ID FROM WorldBank |
SELECT IS_NUMBER(C.grantamt) AS ISN_ATTR, IS_NUMBER(C.id) AS ISN_ID FROM C |
SELECT POWER(totalamt, 2) AS POWERS_A, LENGTH(id) AS LENGTH_ID, PI() AS ThePI FROM WorldBank |
SELECT POWER(C.totalamt, 2) AS POWERS_A, LENGTH(C.id) AS LENGTH_ID, PI() AS ThePI FROM C |