---
title: "Nuevo SQL Engine on MongoDB"
canonical: "https://onesaitplatform-es.refined.site/space/DOC/2215921647/Nuevo%20SQL%20Engine%20on%20MongoDB"
format: markdown
---
> ℹ️ Disponible en versión **2.2.0-hyperblast**
ES | [EN](https://onesaitplatform.atlassian.net/wiki/pages/createpage.action?spaceKey=doc&title=New%20SQL%20Engine%20on%20MongoDB&linkCreation=true&fromPageId=2215921647)
> Macro (toc)
## Introducción
Tras varios meses de pruebas se ha hecho pública la nueva versión del motor SQL para MongoDB.
Este nuevo motor SQL está basado en el proyecto opensource [https://github.com/vincentrussell/sql-to-mongo-db-query-converter](https://github.com/vincentrussell/sql-to-mongo-db-query-converter) al que se han añadido mejoras y cubre la parte de queries de lectura (selects).
Este proyecto, está construido puramente en código Java, por lo que es fácilmente extensible y se pueden incluir nuevas funcionalidades o correcciones de bugs, aportando a la comunidad en el mismo repositorio de github.
A diferencia del motor anterior (Quasar) que funcionaba con la versión de mongo 3.4, este motor, funciona con versiones de mongo desde la 4.0 en adelante. Por defecto, en plataforma, se monta sobre la versión más actual hasta el momento, la 4.4.
Otra gran diferencia, es la eficiencia a la hora de traducir las queries haciendo que el coste de transformación sea mínimo. En Quasar, en ciertos casos, había queries que provocaban un coste alto en la traducción.
## Migración desde el antiguo SQL Engine (Quasar)
Existen varios cambios respecto al antiguo motor Quasar, de modo que, si se plantea una migración, hay que tener en cuenta que el nuevo motor, funciona manteniendo un SQL más estricto y evitando el uso del framework map-reduce, evitando bloqueos de colecciones y la generación de tablas intermedias, priorizando el rendimiento y reduciendo los posibles errores:
- Los datos tipo string son con ' siempre
- Los joins, se limitan al uso sólo de inner join y left join. No es válida la sintaxis de múltiples orígenes comas select * from ontology1, ontology2,….
- El timestamp desde string cambia de timestamp '' a la función to_timestamp('')
- El $oid desde string cambia de OID '' a la función OID('') o toObjectId('')
- Las proyecciones de array u objetos tipo .* .0 [2] [*] {*} no están soportadas, es posible su implementación con funciones
- No se puede concatenar con ||, hay que usar la función concat()
- No se puede usar el alias o el nombre de la ontologías para hacer las proyecciones ni con *. Serían incorrectas estas queries (en su lugar hay que usar **select * from Ontology**):
SELECT c FROM Ontology AS c LIMIT 3
SELECT c.* FROM Ontology AS c LIMIT 3
SELECT Ontology FROM Ontology AS c LIMIT 3
SELECT Ontology.* FROM Ontology AS c LIMIT 3
#### Consideraciones del ObjectID y el _id
A la hora de devolver los datos de tipo objectId de mongoDB, Quasar evitaba devolver el _id
Si se le indicaba explícitamente, quasar lo devuelve en formato string:
En el nuevo motor, por retrocompatibilidad (con un cierto coste debido al parseo de la respuesta), está el parámetro (si no se indica se pone a true por defecto, como es el caso de actualizaciones):
**mongodb-use-legacysql**
Se puede deshabilitar dentro de las configuraciones de plataforma:
Si se deshabilita (por defecto en nuevas instalaciones) se devuelve en formato de mongoDB:
Si se usa un * en el nuevo motor, se devuelve siempre el _id.
## Extensibilidad (Desde 2.2.1-hyperblast)
Existe la posibilidad de ampliar el número de funciones de las que dispone este motor, de modo que desde el controlpanel se pueden incluir nuevas funcionalidades como se puede ver aquí
[https://onesaitplatform.atlassian.net/wiki/spaces/DOC/pages/2215922094](https://onesaitplatform.atlassian.net/wiki/spaces/DOC/pages/2215922094)
## Sintaxis
### Data Types
| Type | Description | Examples |
| --- | --- | --- |
| Null | Indicates missing information. | `null` |
| Boolean | true or false | `true`, `false` |
| Integer | Whole numbers (no fractional component) | `1`, `-2` |
| Decimal | Decimal numbers (optional fractional components) | `1.0`, `-2.19743` |
| String | Text | `'221B Baker Street'` |
| DateTime | Date and time, in ISO8601 format | `to_timestamp('2004-10-19T10:23:54Z')` |
| Object ID | Unique object identifier. | `OID('507f1f77bcf86cd799439011')` |
### Clauses
The following clauses are supported:
| Type | Clauses |
| --- | --- |
| Basic | `SELECT`, `AS`, `FROM` |
| Joins | `LEFT JOIN`, `INNER JOIN`, `JOIN` |
| Filtering | `WHERE` |
| Grouping | `GROUP BY`, `HAVING` |
| Subquery | `FROM (SELECT ...) AS` |
| Paging | `LIMIT`, `OFFSET` |
| Sorting | `ORDER BY` , `DESC`, `ASC` |
| Conditional | `CASE WHEN ELSE` |
The following operators are supported:
| Type | Operators |
| --- | --- |
| String | `LIKE`, `NOT LIKE` |
| Relational | `=`, `>=`, `<=`, `<>`, `IN`, `NOT IN` |
| Boolean | `AND`, `OR`, `NOT` |
| Null | `IS NULL`, `IS NOT NULL` |
| Projection | `foo.bar` |
| Mathematical | `+`, `-`, `*`, `/`, `%` |
The following functions are supported by default (this can be extended with new functions). Those are case insensitive:
| Type | Functions |
| --- | --- |
| String | `CONCAT`, `LOWER`, `UPPER`, `SUBSTRING`, `LENGTH` |
| DateTime | `DATE_PART`, `TO_TIMESTAMP` |
| Arrays | `UNZIP (only in project)`, `ELEMAT` |
| Geo | `GEOWITHIN (only in where)` |
| Set-Level | `DISTINCT` |
| Aggregation | `COUNT`, `SUM`, `MIN`, `MAX`, `AVG, FIRST, LAST, COUNT(DISTINCT ...)` |
| Conversion | `TOINT, TOBOOL, TOSTRING, TOOBJECTID (or OID), TODOUBLE,TOLONG, TODATE, TODECIMAL` |
| Timeserie | `UNZIPTS (only in project)` |
### Ejemplos de uso
#### Queries Básicas:
`select * from ISO3166_1` devuelve la colección, junto con el _id y el contextData:
`select c.ISO3166 from ISO3166_1 as c` devuelve sólo la colección:
#### Where:
Se puede hacer uso de filtros en la clausula where con diferentes operadores:
- Relational: `=`, `>=`, `<=`, `<>`, `IN`, `NOT IN, LIKE, NOT LIKE`
- Boolean: `AND`, `OR`, `NOT`
`select c.ISO3166 from ISO3166_1 as c where c.ISO3166.name='Zambia':`
`select c.ISO3166 from ISO3166_1 as c where c.ISO3166.name='Zambia' and c.ISO3166.language='EN':`
`select c.ISO3166.name as name from ISO3166_1 as c where c.ISO3166.name like 'S%' and c.ISO3166.language='EN':`
`select c.ISO3166.name as name from ISO3166_1 as c where c.ISO3166.name in ('Sudan','Suriname') and c.ISO3166.language='EN':`
#### Group/Having:
`select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by c.ISO3166.language `
`or `
`select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by name:`
`select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by c.ISO3166.language having count(*) > 250 `
`or `
`select c.ISO3166.language as name, count(*) as c from ISO3166_1 as c group by name having c > 250:`
#### Limit/offset
`select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name limit 1:`
`select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name offset 1:`
`select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name limit 3 offset 1`
`or`
`select c.ISO3166.name as name, count(*) as c from ISO3166_1 as c where c.ISO3166.name like 'S%' group by name offset 1 limit 3 (retrocompatibility)`:
#### Case when else (Desde 2.2.1-hyperblast)
Es posible usar la sintaxis case when default como proyección o en la parte de group by (para hacer agrupaciones condicionales aunque como en cualquier group by tiene que ser un campo que esté en la proyección o el alias del mismo). Es obligatorio usar la clausula default.
`select h.Helsinki.population as population, case when h.Helsinki.population between 0 and 50000 then 'low' else 'high' end from HelsinkiPopulation as h`
`select case when h.Helsinki.population between 0 and 50000 then 'low' else 'high' end as grouprange, count(*) as c from HelsinkiPopulation as h group by cca`
#### Mathematical operators (Desde 2.2.1-hyperblast)
Es posible usar operadores matemáticos en las operaciones SQL
`select h.Helsinki.population as pop, h.Helsinki.population+1000 as pop2 from HelsinkiPopulation as h`
`select toInt(c.Helsinki.year/1000) as m, sum(c.Helsinki.population) as s, sum(c.Helsinki.population_men) as sm, sum(c.Helsinki.population_women) as sw, concat(toString(toInt(10000*sum(c.Helsinki.population_men)/sum(c.Helsinki.population))/100),'%') as percentm, concat(toString(toInt(10000*sum(c.Helsinki.population_women)/sum(c.Helsinki.population))/100),'%') as percentw from HelsinkiPopulation as c group by m`
#### Function NOW()
Esta función de plataforma devuelve la fecha y hora del sistema, se puede usar en cualquier parte de la query y añadirá esta fecha en formato string. Si queremos parsearla a fecha de mongo y operar con la misma podemos hacer un **toDate(now())**
**NOW(“format“,'unitTime', amount)**
- “**format**“: formateo de la fecha, por defecto se usa "yyyy-MM-dd'T'HH: mm: ss'Z '"
- '**unitTime**': unidad de tiempo para incrementar o disminuir el número de horas, días, ... los valores posibles de unitTime son: 'year', 'month', 'date', 'hour', 'minute', 'second ',' millisecond '
- "**amount**": entero positivo o negativo, con la cantidad de unitTime a añadir o resta sobre la fecha del sistema
`select Helsinki, now() as nowstr, toDate(now()) as nowdate from HelsinkiPopulation LIMIT 3`
#### Joins/Subqueries
Es posible hacer joins de tipo inner join y left join incluso unirlos con subqueries
`select re.countrysrc as countrysrc,re.countrydest as countrydest,re.count, iso.ISO3166.latitude as latitude, iso.ISO3166.longitude as longitude from ( select rx.routesexten.countrysrc As countrysrc, rx.routesexten.countrydest As countrydest, count(re.routesexten.countrysrc) As count from routesexten as rx group by rx.routesexten.countrysrc, rx.routesexten.countrydest order by count desc) As re inner join ISO3166_1 As iso on re.countrydest = iso.ISO3166.name`
#### Union all (Desde 2.2.1-hyperblast)
Con esta clausula puedes añadir datos de otras queries (los datos duplicados no serán eliminados)
`select 1 as v from Restaurants limit 1 union all `
`select 2 as v from Restaurants limit 1 union all `
`select 3 as v from Restaurants limit 1`
Debido a restricciones de mongodb, se limita el uso a “union all” no a “union“. Por lo tanto, si es necesaria una union de datos sin duplicados, estos deberán agruparse (group by) después de hacer la union de los mismos.
#### Functions (Desde 2.2.1-hyperblast)
Se dispone de varias funciones incluidas en el motor por defecto (estas son ampliables mediante configuración). Se usan en modo case insensitive por lo que pueden escribirse indistintamente en mayúsculas o minúsculas:
| Type | Name | Params | Places | Example | Comments |
| --- | --- | --- | --- | --- | --- |
| String | CONCAT | 1..* String or column with type string | all | `select concat('Cuisine ', c.Restaurant.cuisine) from Restaurants as c` | Return combination of all strings |
| String | LOWER, UPPER | 1 String or column with type string | all | `select concat('Cuisine ', upper(c.Restaurant.cuisine)) from Restaurants as c` | lowercase/uppercase of string |
| String | SUBSTRING | 3 String or column with type string, index (integer), length (integer) | all | `select concat('Cuisine ', substring(c.Restaurant.cuisine,2,3)) from Restaurants as c` | substring of string |
| String | LENGTH | 1 String or column with type string | all | `select length(c.Restaurant.cuisine) from Restaurants as c` | length of string |
| String | TRIM | 1 String or column with type string | all | `SELECT trim(' aaa '), trim(c.Restaurant.borough) FROM Restaurants AS c LIMIT 300` | trim a string |
| DateTime | DATE_PART | 2 part type ("month","year","dayOfMonth","hour","minute","second",
"millisecond","dayOfYear","dayOfWeek","week"), timestamp or field with timestamp type | all | `SELECT date_part('year',timestamp) as year, date_part('month',timestamp) as month FROM QA_DETAIL AS c LIMIT 3`
`SELECT date_part('dayOfMonth',timestamp) as day, count(*) FROM QA_DETAIL AS c group by day` | get date part from timestamp |
| DateTime | TO_TIMESTAMP/TIMESTAMP | 1 timestamp or field with timestamp type | all | `SELECT timestamp FROM QA_DETAIL AS c where timestamp > to_timestamp('2018-10-24T23:00:00.000Z') LIMIT 3`
`SELECT timestamp FROM QA_DETAIL AS c where timestamp > timestamp('2018-10-24T23:00:00.000Z') LIMIT 3` | convert string to timestamp |
| Arrays | UNZIP | 2 array type field, boolean (preserve null and empty arrays in unzip) | project | `select unzip(Restaurant.grades,true) from Restaurants` | unzip array in multiple instances. Second param enable/disable preserve null and empty arrays. **This function can only be used alone in project because of the changing of the number of records. All filters applied are before unzip. You can use this in a subquery for continue operating over array** |
| Arrays | ELEMAT | 1 array type field | all | `select elemat(Restaurant.grades,1) from Restaurants` | get element by position in array |
| Geo | GEOWITHIN | 2 geometry point field, geometry | where | `select * from Supermarkets where geoWithin(Supermarkets.geometry, '{type : "Polygon" , coordinates: [ [ [-15.44488, 28.137924], [-15.423848, 28.137924], [-15.423848, 28.144054], [-15.44488, 28.144054], [-15.44488, 28.137924] ] ] }')` | find some geometry field point in geometry structure as the second arguments. Only in where because mongoDB limitation |
| Aggregation | COUNT | 1 field or * | project or having | `select count(*) from Supermarkets` | count |
| Aggregation | SUM, MIN, MAX, AVG, FIRST, LAST | 1 field (type depending on operation) | project or having | `select min(c.Supermarkets.status) as maxs from Supermarkets as c` | aggregation functions. Last and first get first or last of a group |
| Conversion | TOINT, TOBOOL, TOSTRING, TOOBJECTID (or OID), TODOUBLE,TOLONG, TODATE, TODECIMAL | 1 field (type depending on operation) | all | `select concat('Cuisine ', UPPER(c.Restaurant.cuisine), ' of length ',toString(length(UPPER(c.Restaurant.cuisine)))) as complex from Restaurants as c` | conversion between type |
| Timeserie | UNZIPTS | 2 window type in String (uppercase), window frequency in String (uppercase) | project | `SELECT unzipts('DAYS','MINUTES') FROM timeserie AS c LIMIT 3` | unzip timeserie into plain strcture returning the instances like when they're inserted. **This function can only be used alone in project because of the changing of the number of records. All filters applied are before unzipts. You can use this in a subquery for continue operating over timeserie** |
#### Count Distinct clause (Desde 4.2.1-predator)
Con esta clausula puedes realizar la operación de conteo de distintos valores de un grupo (el incluido en el distinct) para otro grupo dado (el que está fuera del distinct)
`select count(distinct Restaurant.borough) from Restaurants`
`select Restaurant.cuisine, count(distinct Restaurant.borough, Restaurant.name) from Restaurants`
Por restricciones del lenguaje de consultas de mongodb, no esta permitido la combinación con otros elementos diferentes de columnas.