Introducción
En este laboratorio, explorarás los tipos de datos avanzados de PostgreSQL, centrándote en JSON/JSONB, matrices (arrays) y UUIDs. Aprenderás a almacenar, consultar y manipular datos dentro de estos tipos.
El laboratorio comienza demostrando cómo almacenar y consultar datos JSON y JSONB, incluyendo la creación de una tabla con una columna JSONB, la inserción de datos JSON y el uso de operadores como -> y ->> para extraer valores específicos. Luego, procederás a aprender sobre columnas de tipo matriz y UUIDs.
Almacenar y consultar datos JSON y JSONB
En este paso, aprenderás a almacenar y consultar datos JSON y JSONB en PostgreSQL. PostgreSQL proporciona dos tipos de datos para almacenar información JSON: JSON y JSONB. El tipo de datos JSON almacena una copia exacta del texto JSON de entrada, mientras que el tipo JSONB almacena los datos JSON en un formato binario descompuesto. Por lo general, se prefiere JSONB porque ofrece un mejor rendimiento para consultas e indexación.
Comencemos abriendo la terminal de PostgreSQL. Primero, conéctate a la base de datos labex:
sudo -u postgres psql -d labex
Deberías ver el prompt de PostgreSQL:
labex=#
Ahora, creemos una tabla para almacenar datos JSONB:
CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);
Este comando SQL crea una tabla llamada products. La tabla tiene dos columnas: id (una clave primaria entera autoincremental) y data (una columna JSONB para almacenar datos JSON).
Deberías ver una salida similar a esta:
CREATE TABLE
Ahora, insertemos algunos datos en la tabla products:
INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');
Estos comandos insertan dos filas en la tabla products. Cada fila contiene un objeto JSON con información sobre un producto.
Deberías ver una salida similar a esta para cada inserción:
INSERT 0 1
Para consultar los datos JSON, puedes usar los operadores -> y ->>. El operador -> devuelve un objeto JSON, mientras que el operador ->> devuelve un valor JSON como texto.
Por ejemplo, para recuperar el nombre del primer producto, puedes usar la siguiente consulta:
SELECT data ->> 'name' FROM products WHERE id = 1;
Este comando selecciona el valor asociado con la clave name de la columna data de la tabla products, donde el id es 1. El operador ->> asegura que el resultado se devuelva como texto.
Deberías ver una salida similar a esta:
?column?
----------
Laptop
(1 row)
También puedes consultar objetos JSON anidados. Por ejemplo, para recuperar la primera característica del primer producto, puedes usar la siguiente consulta:
SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;
Este comando primero selecciona la matriz features de la columna data y luego selecciona el elemento en el índice 0 de la matriz. El operador ->> asegura que el resultado se devuelva como texto.
Deberías ver una salida similar a esta:
?column?
----------
16GB RAM
(1 row)
También puedes usar el operador @> para verificar si un objeto JSON contiene un par clave-valor específico. Por ejemplo, para encontrar todos los productos con un precio de 75, puedes usar la siguiente consulta:
SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';
Este comando selecciona el nombre de todas las filas de la tabla products donde la columna data contiene un objeto JSON con una clave price y un valor de 75.
Deberías ver una salida similar a esta:
?column?
----------
Keyboard
(1 row)
¡Excelente! Hemos creado con éxito nuestra primera tabla con datos JSONB y aprendido a consultarlos. Continuaremos trabajando con esta tabla en el siguiente paso para añadir características más avanzadas.
Añadir columnas de tipo matriz a una tabla existente
En este paso, aprenderás a añadir y manipular columnas de tipo matriz (array) en PostgreSQL. Ampliaremos nuestra tabla products existente añadiendo una columna de matriz para almacenar etiquetas (tags). Las columnas de matriz permiten almacenar múltiples valores del mismo tipo de datos en una sola columna, lo cual es útil para almacenar listas de elementos como etiquetas, categorías o características.
Como ya estamos conectados a la base de datos y tenemos nuestra tabla products del paso anterior, añadamos columnas de matriz a nuestra tabla existente:
ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];
Estos comandos añaden dos nuevas columnas a nuestra tabla products existente: name (una cadena de texto) y tags (una matriz de cadenas). El tipo de datos TEXT[] especifica que la columna tags es una matriz de valores de texto.
Deberías ver una salida similar a esta para cada comando ALTER:
ALTER TABLE
Ahora, actualicemos nuestros datos existentes e insertemos nuevos datos con las nuevas columnas:
UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;
Estos comandos actualizan nuestros productos existentes con la información de nombre y etiquetas extraída de los datos JSONB y los nuevos valores de matriz.
Deberías ver una salida similar a esta para cada actualización:
UPDATE 1
Para consultar los datos de la matriz, puedes usar la indexación de matrices. Los índices de matriz en PostgreSQL comienzan en 1.
Por ejemplo, para recuperar la primera etiqueta del primer producto, puedes usar la siguiente consulta:
SELECT tags[1] FROM products WHERE id = 1;
Este comando selecciona el elemento en el índice 1 de la matriz tags de la tabla products, donde el id es 1.
Deberías ver una salida similar a esta:
tags
-----------
electronics
(1 row)
También puedes usar la función UNNEST para expandir una matriz en un conjunto de filas.
Por ejemplo, para recuperar todas las etiquetas de todos los productos, puedes usar la siguiente consulta:
SELECT name, UNNEST(tags) AS tag FROM products;
Este comando selecciona el name y cada tag individual de la matriz tags, creando una nueva fila para cada etiqueta.
Deberías ver una salida similar a esta:
name | tag
---------+-------------
Laptop | electronics
Laptop | computers
Laptop | portable
Keyboard| electronics
Keyboard| accessories
Keyboard| input
(6 rows)
Puedes usar el operador @> para verificar si una matriz contiene un valor específico.
Por ejemplo, para encontrar todos los productos con la etiqueta 'electronics', puedes usar la siguiente consulta:
SELECT name FROM products WHERE tags @> ARRAY['electronics'];
Este comando selecciona el nombre de todas las filas de la tabla products donde la matriz tags contiene el valor 'electronics'.
Deberías ver una salida similar a esta:
name
----------
Laptop
Keyboard
(2 rows)
También puedes usar el operador && para verificar si dos matrices tienen algún elemento en común.
Por ejemplo, para encontrar todos los productos que comparten alguna etiqueta con el primer producto, puedes usar la siguiente consulta:
SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;
Este comando selecciona el nombre de todas las filas de la tabla products (con alias p2) que tienen al menos una etiqueta en común con las etiquetas del primer producto (con alias p1), excluyendo al primer producto mismo.
Deberías ver una salida similar a esta:
name
----------
Keyboard
(1 row)
¡Perfecto! Hemos añadido con éxito columnas de matriz a nuestra tabla existente y aprendido a trabajar con ellas. Nuestra tabla products ahora contiene tipos de datos JSONB y de matriz, dejándola lista para el siguiente paso.
Añadir una columna UUID y aprender a generar UUIDs
En este paso, aprenderás a generar y utilizar UUIDs (Identificadores Únicos Universales) en PostgreSQL. Los UUIDs son números de 128 bits diseñados para ser únicos en el espacio y el tiempo. A menudo se utilizan como identificadores únicos en tablas de bases de datos para evitar conflictos al fusionar datos de diferentes fuentes.
Como continuamos con nuestra sesión y tabla actuales, añadiremos una columna UUID a nuestra tabla products para demostrar la funcionalidad de los UUIDs.
Primero, habilitemos la extensión UUID que proporciona funciones de generación de UUID:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Este comando crea la extensión uuid-ossp si aún no existe.
Deberías ver una salida similar a esta:
CREATE EXTENSION
Ahora, añadamos una columna UUID a nuestra tabla products existente:
ALTER TABLE products ADD COLUMN uuid_id UUID;
Este comando añade una nueva columna UUID a nuestra tabla products existente.
Deberías ver una salida similar a esta:
ALTER TABLE
Ahora podemos actualizar nuestras filas existentes con valores UUID usando la función uuid_generate_v4():
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;
Estos comandos actualizan nuestros productos existentes con valores UUID únicos. La función uuid_generate_v4() genera un nuevo UUID para cada fila.
Deberías ver una salida similar a esta para cada actualización:
UPDATE 1
Para consultar los datos usando UUIDs, puedes usar los valores UUID en tus cláusulas WHERE. Dado que los UUIDs se generan aleatoriamente, primero veamos nuestros datos actuales:
SELECT id, name, uuid_id FROM products;
Este comando selecciona todas las columnas de la tabla products para ver los UUIDs generados junto con nuestros otros datos.
Deberías ver una salida similar a esta (tu UUID será diferente):
id | name | uuid_id
----+----------+--------------------------------------
1 | Laptop | 8f14e45f-ea7b-4f9f-a2b0-73f9c3f85a9b
2 | Keyboard | c9f0f895-fb98-4635-bd31-4f7f4d8f9e7a
(2 rows)
Ahora, usa ese UUID en el siguiente comando, reemplazando <YOUR_UUID_HERE> con el UUID real que recuperaste:
SELECT name FROM products WHERE uuid_id = '<YOUR_UUID_HERE>';
Este comando selecciona el name de la tabla products donde el uuid_id coincide con el UUID especificado.
Deberías ver una salida similar a esta (dependiendo del UUID que hayas elegido):
name
----------
Laptop
(1 row)
Mantén la tabla products y la extensión uuid-ossp activas, ya que el siguiente paso continuará utilizándolas.
Extraer datos de tipos avanzados
En este paso final, practicarás la extracción de datos de todos los tipos de datos avanzados que hemos añadido a nuestra tabla products a lo largo de este laboratorio. Nuestra tabla ahora contiene columnas JSONB, de matriz y UUID, proporcionando un ejemplo completo de los tipos de datos avanzados de PostgreSQL.
Como continuamos con nuestra sesión y tabla existentes, podemos comenzar a trabajar inmediatamente con los datos que hemos estado construyendo en los pasos anteriores.
Primero, añadamos algunos datos JSONB adicionales para que nuestros ejemplos de extracción sean más completos:
UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;
Estos comandos actualizan nuestros datos JSONB existentes con información más detallada, incluyendo objetos anidados y campos adicionales.
Deberías ver una salida similar a esta para cada actualización:
UPDATE 1
Ahora practiquemos la extracción de datos de todos nuestros tipos de datos avanzados. Para extraer datos de la columna JSONB data, puedes usar los operadores -> y ->>. Por ejemplo, para extraer la marca (brand) de nuestros datos actualizados:
SELECT data ->> 'brand' FROM products WHERE id = 1;
Este comando recupera el valor asociado con la clave brand de la columna data.
Deberías ver una salida similar a esta:
?column?
----------
Dell
(1 row)
Para extraer datos anidados de la columna JSONB, puedes encadenar los operadores -> y ->>. Por ejemplo, para extraer la especificación de RAM:
SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;
Este comando recupera el valor asociado con la clave ram dentro del objeto specs.
Deberías ver una salida similar a esta:
?column?
----------
16GB
(1 row)
Para extraer datos de la columna tags (matriz), puedes usar la indexación de matrices, como aprendiste en el Paso 2. Por ejemplo, para extraer la primera etiqueta del primer producto:
SELECT tags[1] FROM products WHERE id = 1;
Este comando recupera el elemento en el índice 1 de la matriz tags de la tabla products donde el id es 1.
Deberías ver una salida similar a esta:
tags
-----------
electronics
(1 row)
Ahora creemos una consulta completa que extraiga datos de todos nuestros tipos de datos avanzados: JSONB, matrices y UUIDs:
SELECT
id,
name,
data ->> 'brand' AS brand,
data -> 'specs' ->> 'ram' AS ram,
tags[1] AS first_tag,
uuid_id
FROM products;
Este comando recupera datos de todos los tipos de datos avanzados con los que hemos trabajado: el id entero, el name, la brand de la columna JSONB data, la especificación anidada ram, el primer elemento de la matriz tags y el uuid_id.
Deberías ver una salida similar a esta:
name | ?column? | tags
----------+------------+-----------
Laptop | Dell | electronics
Keyboard | Logitech | electronics
(2 rows)
¡Excelente! Has trabajado con éxito con los tres tipos de datos avanzados de PostgreSQL en una sola tabla. Este ejemplo completo demuestra cómo JSONB, las matrices y los UUIDs pueden utilizarse juntos para crear esquemas de base de datos flexibles y potentes.
Cuando hayas terminado con todos los pasos, puedes salir de la terminal de PostgreSQL escribiendo:
\q
También puedes optar por mantener la tabla para seguir experimentando con los tipos de datos avanzados de PostgreSQL.
Resumen
En este laboratorio, construiste y trabajaste progresivamente con una tabla products completa que demuestra los tipos de datos avanzados de PostgreSQL en acción. Comenzaste creando una tabla con columnas JSONB y aprendiste a almacenar y consultar datos JSON usando operadores como -> y ->>.
Luego, ampliaste la tabla añadiendo columnas de matriz, aprendiendo a almacenar múltiples valores en una sola columna y a consultarlos mediante indexación de matrices y funciones como UNNEST. A continuación, añadiste la funcionalidad UUID habilitando la extensión uuid-ossp y añadiendo una columna UUID para generar identificadores únicos.
Finalmente, practicaste técnicas integrales de extracción de datos que combinaron los tres tipos de datos avanzados (JSONB, matrices y UUIDs) en consultas sofisticadas. Este enfoque paso a paso demostró cómo estos tipos de datos pueden trabajar juntos en esquemas de bases de datos del mundo real, proporcionando flexibilidad y capacidades de consulta potentes para aplicaciones modernas.
