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 para cada una de las bases de datos soportadas.

Aquí encontrarás las guías de referencia de este lenguaje:

Operaciones SELECT

Tipos de datos

Cláusulas

Se soportan las siguientes cláusulas:

Se soportan las siguientes operaciones:

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:

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="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 sobre números y cadenas. Algunos operadores y funciones comunes para cadenas incluyen:

Ejemplos:

select c.ISO3166.number/100+1000 as calculated 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"

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,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 <field>
  WHEN <value1> THEN <result1>
  WHEN <value2> THEN <result2>
  ...
  [ELSE <elseResult>
  END)
FROM "<path>"

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).

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)