PostgreSQL + JSON
JSON
-JavaScript Object Notation - Notación de Objetos
de JavaScript
-Leerlo y escribirlo / Interpretarlo y generarlo
-Estructura universal -Objeto
-Registro
-Colección de pares de clave/valor -Estructura
-Diccionario
-Tabla Hash
-Lista de claves
-Arreglo Asociativo
ESTRUCTURA JSON
ESTRUCTURA JSON
Puede contener o formarse como Arrays
¿CÓMO SON LOS VALORES?
Pueden anidarse
¿CÓMO QUEDARÍA?
{
”estado”: “Miranda”,
”codigo”: “17”,
”municipios”: [
[
”nombre”: “Sucre”,
”codigo”: “98”
]
],
”pais”: “Venezuela”
}
¿DÓNDE SE UTILIZA?
- Formatos ligeros y rápidos para el intercambio de
información.
-2000 Douglas Crockford
-Utilizado en la mayoría de base de datos no
relacionales.
¿JSON CON POSTGRESQL?
¿Sistemas más versátiles?
Mantenibilidad y rendimiento de los JOINS!
NoSQL
¿Esquemas? ¿Normalización? ¿Transacciones?
¿JSON CON POSTGRESQL?
Postgresql no pretende sacrificar las características
de una BD relacional
Incluye diversas soluciones:
➔ HSTORE
➔ JSON
Tipos de Datos
➔ JSONB (No BSON)
Se apoyan en el SQL para trabajar con objetos JSON
JSON
HSTORE POTGRESQL > 8.4
Almacenamiento clave-valor sencillo.
CREATE EXTENSION hstore;
No admite arrays u objetos dentro de su
estructura.
Primera aproximación…
CREATE TABLE prueba (
id SERIAL,
nombre TEXT,
detalle HSTORE,
PRIMARY KEY (id)
);
JSON EN POSTGRESQL > 9.3
Representación en texto plano.
Permite arrays y objetos dentro de su
estructura.
Los datos se guardan tal cual como ingresaron
(orden).
Permiten operaciones avanzadas.
Permite validar los documentos.
JSONB EN POSTGRESQL > 9.4
Representación binaria.
Impacto en disco.
La información no se guarda con orden.
Permite indices GIN y GIST
Permite acceso avanzado y operadores de
comparación JSON estándar
Guarda la información comprimida.
RÁPIDO y EFICIENTE.
ÍNDICES GIN
Índice Generalizado Invertido - Generalized Inverted Index
Almacenan solo las palabras de tipo tsvector.
El tiempo de creación del índice se puede mejorar aumentando el
maintenance_work_mem
CREATE INDEX name ON table USING GIN (column);
ÍNDICES GIST
Árbol de Búsqueda Generalizado - Generalized Search Tree
El índice puede producir coincidencias falsas.
La pérdida provoca una degradación del rendimiento.
CREATE INDEX name ON table USING GIST (column);
ÍNDICES EN JSON-POSTGRESQL
¿GIN o GIST? ¡Depende!
GIN es tres veces más rápido buscando.
GIN tarda 3 veces más en construirse.
GIN en más lento en actualizaciones
GIN ocupa 2 o 3 veces más espacio que
GIST
Regla General: Datos Estáticos → GIN
Datos Dinámicos → GIST
CREAR DATO TIPO JSON
CREATE TABLE clientes (
id SERIAL,
nombre TEXT,
detalle JSON,
PRIMARY KEY (id)
);
CREAR DATO TIPO JSONB
CREATE TABLE clientes (
id SERIAL,
nombre TEXT,
detalle JSONB,
PRIMARY KEY (id)
);
INSERTAR DATOS JSON y JSONB
INSERT INTO clientes (nombre, detalle) VALUES
(
‘Pedro Perez’,
'{
"edad": 25,
"localizacion": {"estado":”Miranda”, "municipio":"Sucre",
"parroquia":”Petare”},
”telefono”: “02121234567”,
”correo”: “ejemplo@[Link]”
}'
);
INSERTAR DATOS JSON y JSONB
INSERT INTO clientes (nombre, detalle) VALUES
(
‘Pedro Perez’,
'{
"edad": 25,
"localizacion": {"estado":”Miranda”, "municipio":"Sucre",
"parroquia":”Petare”}
}'
);
CONSULTAR DATOS JSON y JSONB
SELECT * FROM clientes;
SELECT detalle -> 'edad' AS edad FROM clientes;
SELECT detalle ->> 'edad' AS edad FROM clientes;
SELECT detalle -> 'localizacion' ->> ‘estado’ AS estado
FROM clientes;
CONSULTAR DATOS JSON y JSONB
SELECT detalle#>'{localizacion, estado}' AS estado
FROM clientes;
SELECT detalle#>>'{localizacion, estado}' AS estado
FROM clientes;
ALGUNOS OPERADORES ADICIONALES
SELECT detalle@>'{"telefono":"02121234567"}' AS estado FROM
clientes;
SELECT detalle ? 'telefono' AS estado FROM clientes;
SELECT detalle->'localizacion' || detalle AS estado FROM clientes;
SELECT detalle - 'localizacion' AS estado FROM clientes;
FUNCIONES BÁSICAS PARA JSON y JSONB
to_json(anyelement) SELECT to_json(detalle) FROM clientes;
to_jsonb(anyelement)
SELECT row_to_json(row(id, nombre)) FROM clientes;
row_to_json(record) SELECT row_to_json(c) FROM (SELECT id, nombre FROM clientes) c;
json_build_object(anyelement) SELECT jsonb_build_object('nombre',
jsonb_build_object(anyelement) nombre) FROM clientes;
FUNCIONES BÁSICAS PARA JSON y JSONB
json_extract_path(json, key) SELECT jsonb_extract_path(detalle, 'localizacion')
jsonb_extract_path(jsonb, key) FROM clientes;
json_extract_path_text(json, key) SELECT jsonb_extract_path_text(detalle,
jsonb_extract_path_text(jsonb, key) 'localizacion') FROM clientes;
json_object_keys(json) SELECT jsonb_object_keys(detalle) FROM
jsonb_object_keys(jsonb) clientes GROUP BY 1;
FUNCIONES BÁSICAS PARA JSON y JSONB
json_typeof(json)
jsonb_typeof(jsonb) SELECT jsonb_typeof(detalle->'edad') FROM clientes;
jsonb_pretty(jsonb) SELECT jsonb_pretty(detalle) FROM clientes;
CONSULTAS SQL AVANZADAS EN DATOS JSON
SELECT * FROM clientes WHERE CAST(detalle->>'edad' as int) > 20;
SELECT COUNT(*) FROM clientes
WHERE detalle->'localizacion'->>'estado' = 'Miranda';
SELECT COUNT(*) FROM clientes WHERE detalle ? 'edad';
SELECT * FROM clientes WHERE
detalle->'localizacion'->>'estado' = 'Miranda' OR
detalle->'localizacion'->>'estado' = 'Sucre';
SELECT nombre,
detalle->'localizacion'->>'estado' as estado,
detalle->'localizacion'->>'municipio' as municipio,
detalle->'localizacion'->>'parroquia' as parroquia
FROM clientes;