The add-in 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 add-in 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 add-in 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 |