DocumentDB Query’s

Escrito por Robert Bermejo

DocumentDB es el servicio NoSQL que nos proporciona Azure como servicio PaaS. Sabemos que las bases de datos NoSQL no tienen esquema y en el caso de DocumentDB guardan su información en formato JSON.

Entonces, si no tenemos esquemas y los datos se guardan en formato JSON ¿Cómo puedo consultar los datos que tengo? La respuesta es mediante lenguaje SQL (Structured Query Language).

En este artículo veremos que sentencias SQL pueden utilizarse y cómo hacerlo.

Esquema de una sentencia SQL en DocumentDB

SELECT [TOP <top_expression>] <select_list>

[FROM <from_sepecification]

[WHERE <filter_condition>}

[ORDER BY <sort_specification>]

Como se puede observar el esquema para realizar consultas es muy familiar si se conoce SQL Cada sentencia consiste en un SELECT acompañado opcionalmente por un FROM, WHERE o ORDER BY. Veamos un ejemplo.

Tenemos la siguiente información en nuestra collection Familias de DocumentDB**:**

1{
2  "id": "FamiliaCompartiMOSS",
3  "padres": [
4    {
5      "apellido": "CompartiMOSS",
6      "nombre": "Revista"
7    },
8    {
9      "apellido": "Apellido1",
10      "nombre": "Nombre1"
11    }
12  ],
13  "hijos": [
14    {
15      "apellido": "Apellido2",
16      "nombre": "Nombre2",
17      "sexo": "mujer",
18      "grado": 1,
19      "mascotas": [
20        {"nombre": "Goofy" },
21        {"nombre": "Mickey" }
22      ]
23    },
24    {
25      "apellido": "Apellido3",
26      "nombre": "Nombre3",
27      "sexo": "mujer",
28      "grado": 8
29    }
30  ],
31  "direccion": {
32    "pais": "España",
33    "ciudad": "BCN"
34  },
35  "contacto": {
36    "email": "email@email.com",
37    "telefono": "000000000"
38  },
39  "registrado": false
40}
41

Y realizamos la siguiente consulta:

1 SELECT {
2  "Nombre": "f.id",
3  "Ciudad": "f.direccion.ciudad"
4} AS Familia FROM Familias f WHERE f.direccion.ciudad = “BCN”
5

El resultado será:

1[
2  {
3    "Familia": {
4      "Name": "FamiliaCompartiMOSS",
5      "Ciudad": "BCN"
6    }
7  }
8]
9

Aunque no tengamos un esquema fijo podemos ver cómo podemos realizar consultas utilizando la potencia de SQL. Ahora vamos a ver más en detalle todas las opciones.

FROM

Clausula opcional, aunque si se aplica un filtro en la query o se proyecta algún campo como resultado, entonces será obligatorio definir esta cláusula. Como en una consulta SQL convencional, la cláusula FROM indica la collection sobre la cual se ejecutará la consulta, también puede identificar un subconjunto de la collection como fuente de la consulta.

Un ejemplo de utilización de un subconjunto de la collection sería:

1SELECT * 
2FROM Familias.hijos 
3

El resultado será:

1 [
2  [
3    {
4      "apellido": "Apellido2",
5      "nombre": "Nombre2",
6      "sexo": "mujer",
7      "grado": 1
8    },
9    {
10      "apellido": "Apellido3",
11      "nombre": "Nombre3",
12      "sexo": "mujer",
13      "grado": 8
14    }
15  ]
16]
17

También se puede utilizar alias en las consultas de la forma convencional: SELECT f.id FROM Familias AS f. Una vez se utiliza el alias la fuente ya no se reconocerá, es decir, si realizáramos la consulta SELECT Familias id FROM Familias AS f la query no funcionaría, como tampoco funcionaria SELECT id FROM Familias AS f.

Por último, también se puede utilizar la cláusula IN, que nos permite realizar iteraciones sobre JSON arrays. La cláusula FROM nos da soporte para iteraciones. Vamos a ver un ejemplo, cogiendo como base el ejemplo anterior:

1SELECT * 
2FROM c IN Familias.hijos 
3

El resultado será:

1[
2  {
3    "apellido": "Apellido2",
4    "nombre": "Nombre2",
5    "sexo": "mujer",
6    "grado": 1
7  },
8  {
9    "apellido": "Apellido3",
10    "nombre": "Nombre3",
11    "sexo": "mujer",
12    "grado": 8
13  }
14]
15

Podemos ver que al aplicar la cláusula IN, todos los resultados se devuelven en una única lista, mientras que en el primer ejemplo devolvía una lista por resultado.

WHERE

El comando WHERE es opcional, y si lo añadimos dentro de una query DocumentDB lo que estamos indicando que condiciones deben satisfacer los resultados que serán incluidos en el resultado de la ejecución.

En el siguiente ejemplo podemos ver como se aplica el operador WHERE:

1SELECT f.direccion
2FROM Familias f 
3WHERE f.id = "FamiliaCompartiMOSS "
4

El resultado será:

1[
2  {
3    "direccion": {
4      "pais": "España",
5      "ciudad": "BCN"
6    }
7  }
8]
9

En el ejemplo anterior hemos aplicado el operador =, pero no es el único que podemos utilizar con esta cláusula, también se pueden utilizar los siguientes:

· Aritméticos: +,-,*,/,% · Lógicos: AND, OR · Strings: || (concatenación) · Comparación: =, !=, >, >=, <, <=, <> · Bits: |, &, ^ · Binarios y unarios: true, false, null, undefined… Un ejemplo de uso de un operador binario sería:

1SELECT * FROM Familias f WHERE f.registrado
2

Esta query nos devolvería todos aquellos documentos que contengan true como valor.

· BETWEEN: Palabra clave que especifica un rango de valores. Puede ser usada con strings o números. Por ejemplo:

1SELECT * 
2FROM Familias.hijos [ 0 ] c 
3WHERE c.grado BETWEEN 1 AND 5
4

· IN: Palabra clave que se utiliza para verificar que un valor esté en un conjunto predefinido de valores. Por ejemplo:

1SELECT *
2FROM Familias 
3WHERE Familias.id IN ('Familia2', 'FamiliaCompartiMOSS')
4

SELECT

Cualquier consulta sobre DocumentDB debe contener la cláusula SELECT. Esta cláusula nos indica los campos que serán devueltos al ejecutarse la query. Por ejemplo, si queremos devolver solo la dirección ejecutaríamos la siguiente query:

1SELECT f.contacto
2FROM Familias f 
3WHERE f.id = " FamiliaCompartiMOSS "
4

El resultado será:

1[
2  {
3    "contacto": {
4      "email": "email@email.com",
5      "telefono": "000000000"
6    }
7  }
8]
9

Dentro del operador SELECT podemos realizar las siguientes operaciones:

· Propiedades Anidadas: Devolver una propiedad que está dentro de otra. Por ejemplo:

1SELECT f.direccion.pais, f.direccion.ciudad
2FROM Familias f 
3WHERE f.id = " FamiliaCompartiMOSS "
4

El resultado será:

1[
2  {
3    "pais": "España",
4    "ciudad": "BCN"
5  }
6]
7

· Proyecciones: Devolvemos el resultado en una estructura propia.

1SELECT {
2  "pais": "f.direccion.pais",
3  "ciudad": "f.direccion.ciudad",
4  "nombre": "f.id"
5} FROM Familias f WHERE f.id = " FamiliaCompartiMOSS "
6

El resultado será:

1[{
2  "$1": {
3    "pais": "España",
4    "ciudad": "BCN",
5    "name": " FamiliaCompartiMOSS "
6  }
7}]
8

· Utilización de Alias: Cuando queremos devolver la estructura propia con un nombre propio.

1SELECT
2{
3  "pais": "f.direccion.pais",
4  "ciudad": "f.direccion.ciudad"
5} AS DireccionInfo, 
6       { "nombre ": f.id } NombreInfo
7FROM Familias f 
8WHERE f.id = " FamiliaCompartiMOSS "
9

El resultado será:

1[{
2  "DireccionInfo": {
3    "pais": "España", 
4    "ciudad": "BCN"
5  },
6  "NombreInfo": {
7    "nombre": " FamiliaCompartiMOSS "
8  }
9}]
10

· Expresiones escalares: SELECT soporta expresiones del tipo constantes, expresiones aritméticas, lógicas…. Por ejemplo:

1SELECT ((2 + 11 % 7)-2)/3   
2

El resultado será:

1[
2  {
3    "$1": 1.33333
4  }
5] 
6

· VALUE: Es una palabra clave que nos permite devolver un valor. Por ejemplo, si lo quisiéramos devolver una constante:

1SELECT VALUE "CompartiMOSS" 
2

El resultado será:

1[
2  "CompartiMOSS"
3]
4

También nos permitiría eliminar etiquetas de los resultados generados:

1SELECT VALUE f.direccion
2FROM Familias f 
3

El resultado será:

1[
2  {
3    "pais": "España",
4    "ciudad": "BCN"
5  }
6]
7

· * : Operador especial que nos devolverá todos los campos de la consulta que realicemos:

1SELECT * 
2FROM Familias f 
3WHERE f.id = " FamiliaCompartiMOSS 
4

El Resultado será:

1[
2  {
3    "id": "FamiliaCompartiMOSS",
4    "padres": [
5      {
6        "apellido": "CompartiMOSS",
7        "nombre": "Revista"
8      },
9      {
10        "apellido": "Apellido1",
11        "nombre": "Nombre1"
12      }
13    ],
14    "hijos": [
15      {
16        "apellido": "Apellido2",
17        "nombre": "Nombre2",
18        "sexo": "mujer",
19        "grado": 1,
20        "mascotas": [
21          { "nombre": "Goofy" },
22          { "nombre": "Mickey" }
23        ]
24      },
25      {
26        "apellido": "Apellido3",
27        "nombre": "Nombre3",
28        "sexo": "mujer",
29        "grado": 8
30      }
31    ],
32    "direccion": {
33      "pais": "España",
34      "ciudad": "BCN"
35    },
36    "contacto": {
37      "email": "email@email.com",
38      "telefono": "000000000"
39    },
40    ]
41

· BETWEEN: Tiene el mismo significado y uso que en el Where.

1SELECT (c.grado BETWEEN 0 AND 10) 
2FROM Familias.hijos [ 0 ] c
3

· Operdores ? (Ternary) y ?? (Coalesce): Estos operadores nos permiten aplicar condiciones. Por ejemplo:

1SELECT (c.grado < 5)? "directo": "indirecto" AS nivelgrado 
2FROM Familias.hijos [ 0 ] c 
3

1SELECT f.nombre ?? f.apellido AS apellido 
2FROM Familias f
3

· TOP: Operador que nos permitirá devolver un número concreto de resultados deseados.

1SELECT TOP 1 * 
2FROM Familias f
3

El resultado será:

1[
2  {
3    "id": "FamiliaCompartiMOSS",
4    "padres": [
5      {
6        "apellido": "CompartiMOSS",
7        "nombre": "Revista"
8      },
9      {
10        "apellido": "Apellido1",
11        "nombre": "Nombre1"
12      }
13    ],
14    "hijos": [
15      {
16        "apellido": "Apellido2",
17        "nombre": "Nombre2",
18        "sexo": "mujer",
19        "grado": 1,
20        "mascotas": [
21          { "nombre": "Goofy" },
22          { "nombre": "Mickey" }
23        ]
24      },
25      {
26        "apellido": "Apellido3",
27        "nombre": "Nombre3",
28        "sexo": "mujer",
29        "grado": 8
30      }
31    ],
32    "direccion": {
33      "pais": "España",
34      "ciudad": "BCN"
35    },
36    "contacto": {
37      "email": "email@email.com",
38      "telefono": "000000000"
39    },
40    "registrado": false
41  }
42]
43

Arrays y Objetos

DocumentDB permite la creación de arrays y/o objetos. Podemos construir arrays de la siguiente forma:

1SELECT [ "f.direccion.ciudad", "f.direccion.pais" ] AS PaisCiudad 
2FROM Familias f
3

El Resultado será:

1[
2  {
3    " PaisCiudad ": [
4      "BCN",
5      "España"
6    ]
7  },
8  {
9    "CiudadState": [
10      "BCN",
11      "BCN"
12    ]
13  }
14]
15

JOINS

DocumentDB nos permite realizar JOINS, pero a diferencia del concepto JOIN de una Base de Datos relacional, no nos sirve para relacionar tablas de forma que podamos obtener los datos deseados, sino que nos permite relacionar el schema consigo mismo, lo que en una base de datos relacional equivaldría a realizar una “self-join”.

El resultado de realizar la operación JOIN es un conjunto de N-tuplas con N-Values por tupla, con valores obtenidos de iterar los todos los alias de las collections sobre sus conjuntos.

Por ejemplo, podemos realizar una JOIN entre la raíz y un hijo, el resultado es el producto cruzado entre los dos objetos JSON.

Supongamos que, para este ejemplo, además de la entrada anterior, disponemos de la siguiente entrada

1{
2  "id": "FamiliaCompartiMOSS2",
3  "padres": [
4    {
5      "apellido": "CompartiMOSS22",
6      "nombre": "Revista"2
7    },
8    {
9      "apellido": "Apellido12",
10      "nombre": "Nombre12"
11    }
12  ],
13  "hijos": [
14    {
15      "apellido": "Apellido22",
16      "nombre": "Nombre22",
17      "sexo": "mujer",
18      "grado": 1,
19      "mascotas": [
20        {"nombre": "Goofy2" },
21        {"nombre": "Mickey2" }
22      ]    
23  ],
24  "direccion": {
25    "pais": "España",
26    "ciudad": "MAD"
27  },
28  "contacto": {
29    "email": "email2@email.com",
30    "telefono": "000000001"
31  },
32  "registrado": true
33}
34

Y realizamos la siguiente query:

1SELECT f.id
2FROM Familias f
3JOIN c IN f.hijos 
4

El Resultado será:

1[
2  {
3    "id": " FamiliaCompartiMOSS2"
4  },
5  {
6    "id": "FamiliaCompartiMOSS"
7  },
8  {
9    "id": "FamiliaCompartiMOSS"
10  }
11]
12

ORDER BY

En caso de añadir este operador, nos permitirá ordenar los resultados de la query de la forma deseada, seguimos teniendo las dos entradas en nuestra collection.

1SELECT f.id, f.direccion.ciudad
2FROM Familias f 
3ORDER BY f.direccion.ciudad
4

El Resultado será:

1[
2  {
3    "id": "FamiliaCompartiMOSS",
4    "ciudad": "BCN"
5  },
6  {
7    "id": " FamiliaCompartiMOSS2",
8    "ciudad": "MAD"
9  }
10]
11

CONCLUSIONES

Como podéis observar, realizar queries sobre DobumentDB es muy similar a realizar queries sobre SQL, todos los operadores que tiene son familiares y funcionan de la forma que esperamos. Query playground es un sitio web proporcionado por Microsoft para poder practicar y ver más en profundidad como realizar queries en DobumentDB.

Destacar que DocumentDB también permite trabajar con funciones del tipo:

· Matemático: ABS, CEILING, EXP…..

· Comprobación de tipos: IS_ARRAY, IS_BOOL…

· Funciones de Strings: CONCAT, CONTAINS, ENDSWITH…

· Funciones de Arrays: ARRAY_CONCAT, ARRAY_CONTAINS…

· Funciones de espacio: ST_DISTANCE, ST_WITHIN…

Para saber más sobre funciones en DocubmentDB, visitar los links del apartado referencias.

REFERENCIAS:

· https://docs.microsoft.com/en-us/azure/documentdb/documentdb-sql-query#built-in-functions

· https://www.documentdb.com/sql/tutorial

· https://www.documentdb.com/sql/demo

Robert Bermejo Arquitecto .Net

roberto.bermejo@sogeti.com @robertbemejo www.robertbermejo.com