Bases relacionales ****************** El moderno estándar |SQL| tiene soporte para incluir dentro de las bases relacionales: * |XML| desde `SQL:2003`_, que se conoce como |SQL|\ /|XML| y permite tanto almacenar información en |XML| como generar salida |XML|. * |JSON| desde `SQL:2016`_ y `SQL:2023`_. En este caso, el soporte es más reciente y limitado al almacenamiento de información en |JSON| (véanse `las funcionalidades del estándar al respecto `_) gracias a la definición de un tipo de dato |JSON| y a la existencia de funciones que permiten extraer valores concretos de este dato. No hay, sin embargo, conversión de salida para obtener una salida |JSON| de datos almacenados en forma tradicional, aunque algunos |SGBD| sí lo permiten como PostgreSQL_ con la `función JSON_AGG `_. .. seealso:: La norma de |SQL| (llamada |ANSI| |SQL| por ser esta institución quien publicó la primera versión) ha ido evolucionando con el tiempo y, en consecuencia, se han publicado `sucesivas versiones `_. Hasta su segunda versión (`SQL-92`_) se ceñía al modelo propiamente relacional, pero a partir de su tercera versión (`SQL:1999`_), empezó a incluir aspectos adicionales como es el caso de estos dos que nos ocupan. La interesante página `modern SQL `_ explica muchas de estas adiciones y sirve para investigar la evolución histórica del lenguaje. |XML| ===== El soporte para |XML| es bastante irregular entre los distintos |SGBD| y mejor en aquellos comerciales (`SQL server`_, `Oracle Database`_, DB2_) que en los libres (MariaDB_, PostgreSQL_ o SQLite_, este último sin soporte en absoluto). No entraremos en mucho detalle, pero apuntaremos algunas de sus posibilidades: * Existe un tipo de datos específico para almacenar |XML|. * Permite la generación de una salida |XML| a partir de datos almacenados en forma relacional a través de distintas funciones (``XMLELEMENT``, ``XMLROOT``, etc.). * Permite la generación de una salida |XML| a partir de datos almacenados en forma |XML| (a través del tipo |XML|) gracias a la función ``XMLQUERY``, que utiliza :ref:`XQuery` para generar la estructura |XML|. * Permite la extracción de valores de un dato de tipo |XML| mediante la función ``EXTRACTVALUE``\ [#]_. que utiliza para referirlos :ref:`XPath`. .. seealso:: Para profundizar en la inclusión de |XML| dentro de los |SGBD| relacionales con ejemplos ilustrativos de uso puede consultar: * `SQL/XML and XPath `_. * `XML Support in Modern SQL Databases `_ (PDF). |JSON| ====== El soporte para |JSON| dentro de |SQL| se ha desarrollado hasta ahora en dos estándares: `SQL:2016`_ que definió cómo acceder a valores dentro de un documento |JSON| o cómo comprobar si una cadena representa un documento |JSON| válido. No define, sin embargo, un tipo específico para |JSON| por lo que los documentos |JSON| son representados, en realidad, como cadenas de texto. `SQL:2023`_ que amplía el soporte añadiendo un tipo específico. .. caution:: A diferencia de lo que ocurre con |XML|, el último estándar no define (al menos, aún) cómo generar una salida en formato |JSON|, aunque hay |SGBD| que ya lo hacen. Los principales |SGBD| relacionales permiten la manipulación de documentos |JSON| en mayor o menor medida. La utilidad de ello se ve cuando se necesita definir una tabla con muchos campos, algunos de los cuales no tienen porqué tener relevancia o valor en todos los registros. Recordemos que el modelo relacional se caracteriza porque todos los registros tienen los mismos campos a diferencia de un documento |JSON| en donde podemos tener una secuencia de objetos, cada uno de los cuales no presenta exactamente las mismas claves. Como consecuencia una solución |SQL| provocaría que necesitáramos crear una tabla con muchísimas columnas (campos), aunque muchos de ellos para muchos registros estarían vacíos. Para ilustrarlo, supongamos que tenemos una tabla en la que almacenamos los datos de nuestros proveedores y sus datos de contacto: .. code-block:: sql CREATE TABLE "Proveedor" ( "id" INTEGER PRIMARY KEY, "nombre" VARCHAR(100) NOT NULL, /* Todos estos datos son de contacto y algunos estarán vacíos */ "tlfo" INTEGER, "fax" INTEGER, "web" VARCHAR(200), "mail" VARCHAR(100), /* En realidad los tipos son más de los aquí expresados */ "dom_tipo_via" CHAR(15) CHECK ("dom_tipo_via" IN ('calle', 'avenida', 'plaza')), "dom_nombre_via" VARCHAR(100), "dom_numero" INTEGER, "dom_subnumero" CHAR(1), "dom_puerta" CHAR(3), "dom_bloque" CHAR(3), "dom_escalera" CHAR(3), "dom_piso" INTEGER, "dom_letra" CHAR(2), "dom_cp" INTEGER, "dom_localidad" VARCHAR(75), "dom_municipio" VARCHAR(75), "dom_provincia" INTEGER ); La tabla tiene muchísimas columnas, porque la casuística de los datos de contacto es muy grande, queremos cubrirla toda (o, al menos, una gran parte) y el modelo relacional nos obliga a crear un campo para todo. En cambio, si echamos manos de |JSON|, podremos definir la tabla de este modo: .. code-block:: sql CREATE TABLE "Proveedor" ( "id" INTEGER PRIMARY KEY, "nombre" VARCHAR(100), "contacto" JSON /* Aquí en realidad se almacenan todos los datos anteriores */ ); Y dentro del campo de contacto que se almacene una estructura |JSON| que facilita no tener definidos siempre todos los campos: .. code-block:: json { "tlfo": 34956010203, "fax": null, "web": "https://www.tuproveedordeconfianza.com/", "mail": "clientes@tuproveedordeconfianza.com", "domicilio": { "tipo_via": "calle", "via": "Callo", "numero": 25, "subnumero": "A" "puerta": null, "bloque": null, "escalera": 2, "piso": 1, "letra": null, "cp": 11001, "localidad": "Cádiz", "municipio": "Cádiz" "provincia": 11 } } .. note:: Hemos expresado los campos con valor nulo para que se vea cómo se reproduce fielmente la estructura de la primera tabla, pero no son necesarios y, de hecho, no estarán cuando tengan este valor. La idea que subyace en la incorporación de |JSON| al almacenamiento es ésta. Para rematar ilustrémoslo utilizando SQLite_, que `tiene soporte para JSON `_, aunque sin llegar a crearse realmente un tipo de dato distinto (o sea, lo que estipula `SQL:2016`_). Descargue :download:`proveedor.sql ` que contiene la creación de la segunda versión propuesta de la tabla\ [#]_ y un par de registros de prueba, y pruebe cómo acceder a los campos individuales del |JSON|: .. code-block:: console $ sqlite /tmp/proveedor.db < proveedor.sql $ sqlite /tmp/proveedor.db sqlite> .tables Proveedor sqlite> .mode columns sqlite> SELECT "nombre", JSON_EXTRACT("contacto", '$.domicilio.localidad') AS 'localidad' FROM "Proveedor"; nombre localidad --------------------- --------- Helados Babuino Cádiz Central de Chucherías Málaga sqlite> SELECT "id", "nombre" FROM "Proveedor" WHERE JSON_EXTRACT("contacto", '$.domicilio.localidad') = 'Cádiz'; id nombre -- --------------- 1 Helados Babuino Como puede verse, estamos usando una sintaxis derivada de :ref:`JSONPath` para referir los valores individuales que contiene el |JSON| contenido en el campo ``contacto``\ [#]_. .. rubric:: Notas al pie .. [#] ``EXTRACTVALUE`` es la función equivalente a ``JSON_EXTRACT`` que veremos más en profundidad bajo el siguiente epígrafe. .. [#] :program:`SQLite` permite indicar un tipo ``JSON``, aunque internamente ese tipo será simplemente ``TEXT``. .. [#] :program:`SQLite` tiene una `operador procedente de PostgreSQL `_ alternativo a JSON_EXTRACT como `azúcar sintáctico `_: .. code-block:: sql SELECT "nombre", "contacto"->'domicilio'->>'localidad' AS 'localidad' FROM "Proveedor"; .. seealso:: Eche un vistazo a `este tutorial de JSON con SQLite `_. Y `SQL:2023`_ ha definido el operador punto (".") con la misma intención: .. code-block:: sql SELECT "nombre", "contacto"."domicilio"."localidad" AS 'localidad' FROM "Proveedor"; aunque para ello :program:`SQLite` no tiene aún soporte. .. |SGBD| replace:: :abbr:`SGBD (Sistema Gestor de Bases de Datos)` .. |SQL| replace:: :abbr:`SQL (Structured Query Language)` .. |BB.RR.| replace:: :abbr:`BB.RR. (Bases Relacionales)` .. |ANSI| replace:: :abbr:`ANSI (American National Standards Institute)` .. _`SQL:2003`: https://en.wikipedia.org/wiki/SQL:2003 .. _`SQL:2023`: https://en.wikipedia.org/wiki/SQL:2023 .. _`SQL:2016`: https://en.wikipedia.org/wiki/SQL:2016 .. _`SQL:1999`: https://en.wikipedia.org/wiki/SQL:1999 .. _`SQL-92`: https://en.wikipedia.org/wiki/SQL-92 .. _PostgreSQL: https://www.postgresql.org/ .. _SQLite: https://sqlite.org .. _Oracle Database: https://es.wikipedia.org/wiki/Oracle_Database .. _DB2: https://en.wikipedia.org/wiki/IBM_Db2 .. _SQL Server: https://en.wikipedia.org/wiki/Microsoft_SQL_Server .. _MariaDB: https://en.wikipedia.org/wiki/MariaDB