---
title: "SQL en Mongo (legacy)"
canonical: "https://onesaitplatform-es.refined.site/space/DOC/2215921334/SQL%20en%20Mongo%20(legacy)"
format: markdown
---
> ℹ️ **IMPORTANTE**
> ℹ️
> ℹ️ Esta guía aplica al motor SQL de consultas basado en Quasar, y que funcionaba para versiones Mongo hasta la 3.4
> ℹ️
> ℹ️ Si usas el nuevo motor SQL o una versión de Mongo 4.X debes leer esta guía: [Nuevo SQL Engine on MongoDB](https://onesaitplatform.atlassian.net/wiki/spaces/DOC/pages/2215921647)
El Data Hub Semántico tiene entre otros objetivos **desacoplar la ontología de la base de datos subyacente.**
A tal fin, proponemos utilizar el lenguaje de consulta SQL, por lo que la plataforma ofrece un [Motor de Consulta SQL](https://quasar-analytics-documentation.readthedocs.io/en/latest/sql-squared-reference/) para cada una de las bases de datos soportadas.
Aquí encontrarás las guías de referencia de este lenguaje:
> Macro (toc)
# Operaciones SELECT
## Tipos de datos
| **Tipo** | **Descripción** | **Ejemplos** | **Soporte nativo en MongoDB** |
| --- | --- | --- | --- |
| Null | Indica que falta información. | `null` | Sí |
| Boolean | true o false (verdadero o falso). | `true`, `false` | Sí |
| Integer | Números enteros (sin componente fraccionario), | `1`, `-2` | Sí |
| Decimal | Números con decimales (componente fraccionario opcional). | `1.0`, `-2.19743` | Sí |
| String | Texto. | `'221B Baker Street'` | Sí |
| DateTime | Fecha y hora, en formato ISO8601. | `TIMESTAMP '2004-10-19 10:23:54'` | Sí |
| Time | Hora, en formato HH:MM:SS. | `TIME '10:23:54'` | No |
| Date | Fecha, en formato YYYY-MM-DD | `DATE '2004-10-19'` | No |
| Interval | Intervalo de tiempo, en formato ISO8601. | `INTERVAL 'P3DT4H5M6S'` | No |
| Object ID | Identificador único del objeto. | `OID '507f1f77bcf86cd799439011'` | Sí |
| Ordered Set | Lista ordenada que no permite duplicados. | `(1, 2, 3)` | No |
| Array | Lista ordenada que permite duplicados. | `[1, 2, 2]` | Sí |
## Cláusulas
Se soportan las siguientes cláusulas:
| **Tipo** | **Cláusulas** |
| --- | --- |
| Basic | `SELECT`, `AS`, `FROM` |
| Joins | `LEFT OUTER JOIN`, `RIGHT OUTER JOIN`, `INNER JOIN`, `FULL JOIN`, `CROSS` |
| Filtering | `WHERE` |
| Grouping | `GROUP BY`, `HAVING` |
| Conditional | `CASE` , `WHEN`, `DEFAULT` |
| Paging | `LIMIT`, `OFFSET` |
| Sorting | `ORDER BY` , `DESC`, `ASC` |
Se soportan las siguientes operaciones:
| **Tipo** | **Operadores** |
| --- | --- |
| Numeric | `+`, `-`, `*`, `/`, `%` |
| String | `~` , `~*`, `!~`, `!~*`, `LIKE`, `||` |
| Array | `||`, `[ ... ]` |
| Relational | `=`, `>=`, `<=`, `<>`, `BETWEEN`, `IN`, `NOT IN` |
| Boolean | `AND`, `OR`, `NOT` |
| Projection | `foo.bar`, `foo[2]`, `foo{*}`, `foo[*]` |
| Date/Time | `TIMESTAMP`, `DATE`, `INTERVAL`, `TIME` |
| Identity | `OID` |
**Nota:** `~` , `~*`, `!~` y `!~*` son operadores de expresiones regulares. `~*`, `!~` `y``!~*` son preliminares y podrían no funcionar en la versión actual.
**Nota:** El operador `||` para cadenas (strings) concatenará dos cadenas. Por ejemplo, puedes crear un nombre completo a partir de una propiedad de nombre y apellido: `c.firstName || ' ' || c.lastName`. El operador `||` para matrices (arrays) concatenará dos matrices. Por ejemplo, si `xy` es una matriz con dos valores, entonces `c.xy || [0]` creará una matriz con tres valores, donde el tercer valor es cero.
Se soportan las siguientes funciones:
| **Tipo** | **Funciones** |
| --- | --- |
| String | `CONCAT`, `LOWER`, `UPPER`, `SUBSTRING`, `LENGTH`, `SEARCH` |
| DateTime | `DATE_PART`, `TO_TIMESTAMP` |
| Nulls | `COALESCE` |
| Arrays | `ARRAY_LENGTH`, `FLATTEN_ARRAY` |
| Objects | `FLATTEN_OBJECT` |
| Set-Level | `DISTINCT`, `DISTINCT_BY` |
| Aggregation | `COUNT`, `SUM`, `MIN`, `MAX`, `AVG` |
| Identity | `SQUASH` |
## Consultas básicas
select * from ISO3166_1 as c devuelve información y ContextData:
select c.ISO3166 as ISO3166 from ISO3166_1 as c devuelve sólo Datos.
select _id,c from ISO3166_1 devuelve * incluyendo el id del Registro:
## Filtrado
Puedes filtrar un conjunto de resultados usando la cláusula WHERE. Se soportan los siguientes operadores:
- Relationales: `-`, `=`, `>=`, `<=`, `<>`, `BETWEEN`, `IN`, `NOT IN`
- Booleanos: `AND`, `OR`, `NOT`
**Ejemplos:**
select c.ISO3166 as ISO3166 from ISO3166_1 as c where [c.ISO3166.name](http://c.ISO3166.name)="Zambia"
## Operaciones numéricas y con cadenas (strings)
Puedes usar cualquiera de los operadores o funcionados listados en la sección [Cláusulas, Operadores y Funciones](https://quasar-analytics-documentation.readthedocs.io/en/latest/sql-squared-reference/#clauses-operators-functions) sobre números y cadenas. Algunos operadores y funciones comunes para cadenas incluyen:
| **Operador o función** | **Descripción** |
| --- | --- |
| `||` | Concatenata. |
| `LOWER` | Convierte a minúscula. |
| `UPPER` | Convierte a mayúscula. |
| `SUBSTRING` | Devuelve una sub-cadena. |
| `LENGTH` | Devuelve la longitud de la cadena. |
**Ejemplos:**
select c.ISO3166.number/100+1000 as calculated from ISO3166_1 as c where [c.ISO3166.name](http://c.ISO3166.name)="Zambia" and c.ISO3166.language="EN"
select [c.ISO3166.name](http://c.ISO3166.name) as name from ISO3166_1 as c where [c.ISO3166.name](http://c.ISO3166.name) like "S%" and c.ISO3166.language="EN"
## Fechas y horas
Puedes filtrar por fecha y hora con los operadores TIMESTAMP, TIME y DATE. Además, también puedes usar el operador DATEPART para seleccionar parte de una fecha, como el día.
**Nota:** Algunas bases de datos convierten automáticamente las cadenas de caracteres en fechas o en fechas y horas. SlamData no realiza esta conversión, ya que la base de datos subyacente no tiene un esquema ni un tipo fijo para ningún campo. Como resultado, una expresión como `WHERE ts > '2015-02-10'` compara los campos de valor cadena `ts` con la cadena `'2015-02-10'`, en lugar de hacer una comparación de fechas. Si quieres incluir fechas y marcas de tiempo literales en tus consultas SQL, debes utilizar los operadores de conversión de tiempo, que aceptan una cadena y devuelven un valor del tipo apropiado. Por ejemplo, el fragmento anterior podría convertirse en `WHERE ts > DATE '2015-02-10'`, que busca campos `ts` con valor de fecha y los compara con la fecha `2015-02-10`.
**Nota:** Si los datos de tu base de datos no utilizan un tipo nativo de fecha/hora, y en vez eso tú almacenas tus timestamp como milisegundos desde un epoch en un valor numérico, entonces debes comparar números o utilizar la función TO_TIMESTAMP.
**Filtro basado en una marca de tiempo (fecha y hora).**
Usael operador TIMESTAMP para convertir una cadena en una fecha y hora. La cadena debe tener el formato `YYYY-MM-DDTHH:MM:SS`.
select c.type,[c.name](http://c.name),c.dateLastUpdate,DATE_PART("year",c.dateLastUpdate) as year from CLM_BusStop as c
select c from CLM_BusStop as c WHERE c.CLM_BusStop.lastUpdateMillis > TO_TIMESTAMP(1446335999)
select c from CLM_BusStop as c WHERE c.CLM_BusStop.lastUpdate > TIMESTAMP('2018-11-06T12:00:00.000Z')
### Función NOW()
Esta función **devuelve la fecha actual del sistema**. Puedes usarla en el where de tus consultas para traer datos.
**NOW(“format“,'unitTime', amount)**
- “**format**“: ("formato") le daremos el formato que necesitamos a la fecha, por ejemplo yyyy-MM-dd'T'HH: mm: ss'Z '"
- '**unitTime**': ('unidad de tiempo') para la echa, podemos aumentar o disminuir un número de horas, días, ... los valores posibles para 'unitTime' son: 'year', 'month', 'date', 'hour', 'minute', 'second','millisecond' (respectivamente: 'año', 'mes', 'fecha', 'hora', 'minuto', 'segundo ',' milisegundo')
- "**amount**": (cantidad) es un valor entero, positivo o negativo, que se suma o se resta en función de la unitTime seleccionada.
Ejemplos:
select * from MyOntology as c where c.date.timestamp>NOW()
## Agrupación
SQL en Mongo te permite agrupar los datos por campos y por partes de la fecha.
**Agrupación en base a un solo campo.**
Usa GROUP BY para agrupar los resultados por un campo.
Ejemplo:
`SELECT c.age, COUNT(*) AS cnt
FROM Users as c GROUP BY c.age`
`SELECT c.age, c.gender, COUNT(*) AS cnt
FROM Users as c GROUP BY c.age, c.gender`
**Agrupación en base a parte de la fecha.**
Usa la función DATE_PART para agrupar por una parte de una fecha, como el mes.
Ejemplo:
`SELECT DATE_PART('day', c.ts) AS day, COUNT(*) AS cnt
FROM Events as c
GROUP BY DATE_PART('day', c.ts)`
**Filtrado dentro de un grupo.**
Filtra los resultados dentro de un grupo añadiendo una cláusula HAVING seguida de un predicado booleano.
Ejemplo:
`SELECT DATE_PART('day', c.ts) AS day, COUNT(*) AS cnt
FROM Events as c
GROUP BY DATE_PART('day', c.ts)
HAVING c.gender = 'female'`
**Agrupación doble**
Realiza operaciones de agrupación doble poniendo operadores dentro de otros operadores. El operador interior se ejecutará sobre cada grupo creado por la cláusula GROUP BY, y el operador exterior se ejecutará sobre los resultados del operador interior.
Ejemplo:
Esta consulta devuelve la población media de los estados. La función externa de agregación (AVG; average o media) opera sobre los resultados de la agregación interna (SUM; suma) y la cláusula GROUP BY.
`SELECT AVG(SUM(p.pop)) FROM Population as p
GROUP BY p.state`
## Datos anidados y matrices
A diferencia de las bases de datos relacionales, muchas bases de datos NoSQL permiten anidar datos (es decir, los datos pueden ser objetos) y contener matrices.
**Anidamiento**
El anidamiento se representa mediante niveles separados por un punto (.).
**Matrices**
Los elementos que son matrices (arrays) se representan con el índice de la matriz entre corchetes ([n]).
Ejemplo:
`SELECT c.profile.allAddress[0].street.number
FROM Users as c`
**Aplanamiento**
Puedes extraer todos los elementos de una matriz o todos los valores de los campos simultáneamente, eliminando esencialmente los niveles y aplanando los datos. Usa el asterisco entre corchetes ([*]) para extraer todos los elementos de la matriz.
Ejemplo:
`SELECT c.profile.allAddresses[*]
FROM Users as c`
Usa el asterisco entre llaves ({*}) para extraer todos los valores de los campos.
Ejemplo:
`SELECT c.profile.{*} FROM Users as c`
**Filtrado mediante matrices**
Puedes filtrar utilizando los datos de todos los elementos de la matriz utilizando el asterisco entre corchetes ([*]) en una cláusula WHERE.
Ejemplo::
`SELECT DISTINCT * FROM Users as c
WHERE c.profile.allAddresses[*].street.number = '221B'`
## Paginación y ordenación
**Paginación**
La paginación se utiliza para dividir los resultados grandes en trozos más pequeños. Usa el operador LIMIT para establecer el número de resultados a devolver y el operador OFFSET para establecer el índice en el que deben empezar los resultados.
Ejemplo (Limitar los resultados a 20 entradas):
`SELECT u FROM Users as u LIMIT 20`
Ejemplo (Limitar los resultados a 20 entradas empezando en el 10):
`SELECT u FROM Users as u OFFSET 10 LIMIT 20`
**Ordenación**
Usa la cláusula ORDER BY para ordenar los resultados. Puedes especificar uno o más campos para la ordenación y puedes utilizar operadores en los argumentos de ORDER BY. Usa ASC para ordenación ascendente y DESC para ordenación descendente.
Ejemplo (Ordenar los usuarios por edad ascendente):
`SELECT u
FROM Users as u
ORDER BY u.age ASC`
## Joins
Usa el operador JOIN para unir diferentes colecciones.
Ejemplos:
Este ejemplo devuelve los nombres de los empleados y los nombres de los departamentos a los que pertenecen, haciendo coincidir el ID del departamento del empleado con el ID del departamento, donde ambos ID son de tipo ObjectID.
`SELECT emp.name, dept.name
FROM Employee as emp
JOIN Department as dept
ON dept._id = emp.departmentId`
## Condicionales y nulos
**Condicionales**
Usa la expresión CASE para proporcionar lógica if-then-else a SQL². La sintaxis CASE es:
`SELECT (CASE
WHEN THEN
WHEN THEN
...
[ELSE
END)
FROM ""`
Ejemplo:
El siguiente ejemplo traduce los valores numéricos de la cadena a números reales.
select
(CASE c.Ticket.number
WHEN 'one' THEN 1
WHEN 'two' THEN 2
END)
as nameParsed
from Ticket as c
## Consideraciones sobre el ID
MongoDB tiene reglas especiales sobre los campos llamados `_id`. Por ejemplo, deben ser únicos, lo que significa que algunas consultas (como `SELECT myarray[*] FROM foo`) introducirán duplicados que MongoDB no permitirá. Además, otras consultas cambian el valor de `_id` (como la agrupación).
**Para filtrar por ****_id****, **primero debes convertir una cadena en un objeto ID, utilizando la función `OID`. Por ejemplo:
SELECT * FROM Ticket WHERE _id = OID( 'abc123')
Por defecto, el campo `_id` no aparecerá en un conjunto de resultados. Sin embargo, puedes especificarlo seleccionando el campo `_id`. Por ejemplo:
SELECT _id, u AS email FROM users as u
## Consideraciones sobre el comodín *
Por razones de rendimiento, te recomendamos que evite usar el comodín '*' en los SELECT, y que lo sustituyas por proyecciones o campos explícitos:¡
SELECT * FROM Ticket -> SELECT t FROM Ticket as t
SELECT * FROM Ticket -> SELECT t.Ticket.name as name, t.Ticket.number as number FROM Ticket as t
## Ejemplos complejos
A continuación mostramos algunos ejemplos de consultas que hemos utilizado en diferentes proyectos:
`select `
` t.tempo, e.lotto, count(1) `
` from TempoDesc as t `
` inner join `
` (select `
` c.lotto, c.epochEnd - d.minStart as durataTotale `
` from `
` (select `
` min(epochStart) as minStart, lotto `
` from Eventi1 `
` group by lotto) as d `
` inner join Eventi1 as c `
` on c.lotto=d.lotto ) as e `
` on e.durataTotale <= t.tempo `
` group by t.tempo, e.lotto`
`select `
` re.countrysrc,re.countrydest,re.count, iso.ISO3166.latitude , iso.ISO3166.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`
` order by `
` re.count desc `
` limit 10`
# Operaciones de actualización (UPDATE)
También puedes realizar operaciones de actualización (UPDATE) en SQL. Aquí explicaremos las peculiaridades de este tipo de directivas SQL.
**Sintaxis simple**
Como en el SQL estándar, la directiva de actualización se construye de la siguiente manera:
UPDATE column SET attributes WHERE attributesFilter
A continuación se muestran algunos ejemplos de operaciones básicas de Update:
Pongamos como ejemplo de trabajo la ontología de Restaurantes con la siguiente estructura:
update Restaurants set Restaurant.borough="Queens" where Restaurant.borough="Manhattan"
update Restaurants set Restaurant.borough="Queens", Restaurant.restaurant_id=5400 where Restaurant.borough="Manhattan" and Restaurant.borough="Dj Reynolds Pub And Restaurant"
## ObjectId
Si quieres filtrar por el ObjectId de Mongo, no necesitas usar ninguna función especial: basta con indicar el valor entre comillas del ObjectId, por ejemplo:
update Restaurants set Restaurant.borough="Manhattan", Restaurant.restaurant_id=5400 where _id="5d14883e29a91330a9625d0c"
## ISODates
Como con el ObjectId, si quieres trabajar con fechas de Mongo, sólo tienes que poner el valor citado de la fecha en formato ISODate (yyyy-MM-dd'T'HH:mm:ss.SSS'Z'):
update ThermometerData set ThermometerData.value=60 where ThermometerData.timestamp="2019-06-26T13:57:01.000Z"
La consulta compilada:
Resultado:
## Booleanos
Si quieres establecer o filtrar valores booleanos, tienes que utilizar la función SQL Boolean() de este modo:
update ThermometerData set ThermometerData.bool=Boolean(true) where ThermometerData.timestamp="2019-06-26T13:57:01.000Z"
## Matrices
Si quieres añadir elementos a un campo matriz, tienes que utilizar la función APPEND().
APPEND(i, valor) acepta dos parámetros:
- i: Es opcional (por defecto es la última posición del array). Indica la posición en la que se va a introducir el valor.
- valor: Es el valor que se va a insertar en el array. Puede ser un objeto, una cadena, un número... Este parámetro puede ser también un array de valores.
Por ejemplo:
update ThermometerData set ThermometerData.array=APPEND("{\"Property\":\"value\"}")
update ThermometerData set ThermometerData.array=APPEND([true, false])
update ThermometerData set ThermometerData.array=APPEND([true, false, 45, "somestring", "2019-06-26T13:57:01.000Z"])
El resultado es:
**Nota importante:** Si quieres empujar un ISODate (fecha de Mongo) al array, querrás rodear el valor de isodate por una función ISODate:
update ThermometerData set ThermometerData.array=APPEND([true, false, 45, "somestring", ISODate("2019-06-26T13:57:01.000Z")])
# Operaciones de borrado (DELETE)
La directiva Delete funciona como en el SQL estándar.
DELETE FROM column WHERE attributes
Las operaciones ObjectId, ISODate y Boolean funcionan como en las actualizaciones. Aún no se puede utilizar ningún tipo de operaciones de matrices en esta directiva.
Ejemplos:
DELETE FROM Restaurants WHERE _id="5d14883e29a91330a9625d0c"
DELETE FROM Restaurants WHERE timestamp="2019-06-26T13:57:01.000Z"
DELETE FROM Restaurants WHERE isClosed=Boolean(true)
> Macro (__confluenceADFMigrationUnsupportedContentInternalExtension__)