Consulta y filtrado de campos JSON en PostgreSQL

Las funciones JSON de PostgreSQL te permiten almacenar, consultar y manipular datos JSON dentro de las tablas de tu base de datos. Son esenciales para las aplicaciones que requieren un esquema flexible o necesitan procesar datos JSON directamente en la base de datos.

Comprender JSON y JSONB

`JSON` y `JSONB` son tipos de datos de PostgreSQL que se utilizan para almacenar datos JSON. `JSON` almacena los datos en formato de texto, mientras que `JSONB` los almacena en formato binario descompuesto, lo que permite una indexación y consulta eficientes.

Utilización

Las funciones JSON se utilizan en PostgreSQL para acceder y filtrar datos JSON almacenados en columnas de tipo `JSON` o `JSONB`. Permiten recuperar y manipular datos complejos proporcionando métodos para extraer elementos, filtrar arreglos y modificar objetos JSON.

SELECT json_column->'key'
FROM table_name
WHERE json_column->>'key' = 'value';

En esta sintaxis, `->` se utiliza para extraer un objeto JSON, mientras que `->>` extrae texto JSON.

Ejemplos

1. Extraer un campo JSON

SELECT data->'name' AS name
FROM users;

Esta consulta extrae el campo `nombre` de la columna JSON `datos` de la tabla `usuarios`.

2. Filtrar con JSONB

SELECT *
FROM orders
WHERE order_data->>'status' = 'shipped';

Aquí, la consulta filtra las filas de la tabla `orders` en las que la clave `status` de la columna JSONB `order_data` tiene el valor `shipped`.

3. Filtrado de Arreglos JSONB

SELECT id, info
FROM products
WHERE info->'tags' @> '["electronics"]';

Esta consulta selecciona `id` e `info` de la tabla `products` en la que el arreglo `tags` de la columna JSONB `info` contiene el valor "electronics".

4. Actualizar datos JSON

UPDATE users
SET data = jsonb_set(data, '{address, city}', '"New City"')
WHERE id = 1;

Esta consulta actualiza el campo `ciudad` dentro del objeto `dirección` en la columna JSONB `datos` para el usuario con `id` 1.

Consejos y buenas prácticas

• Utiliza JSONB para la indexación. Prefiere `JSONB` a `JSON` para mejorar el rendimiento de la indexación y la búsqueda.
• Aprovecha los índices GIN. Crea índices GIN en columnas JSONB para acelerar las consultas de contención.
• Extrae con cuidado. Utiliza `->` para los objetos JSON y `->>` para la extracción de texto para reducir los errores de fundición de tipos.
• Mantén las consultas específicas. Estructura tus consultas para minimizar la cantidad de datos JSON procesados, mejorando el rendimiento.
• Considera la normalización. Para los campos JSON que se consultan con frecuencia, considera la posibilidad de normalizar los datos en columnas o tablas separadas para mayor eficacia.
• Comprende las diferencias de rendimiento. En general, `JSONB` es más rápido para las operaciones de lectura debido a su formato binario, pero puede requerir más espacio de almacenamiento.
• Cuidado con las implicaciones de almacenamiento. `JSONB` puede tener mayores requisitos de almacenamiento debido a su representación binaria, así que elige el tipo de datos en función de tu caso de uso específico.