Almacenando y Consultando JSON en SQLite: Ejemplos y Mejores Prácticas

JSON, o JavaScript Object Notation, es un formato para almacenar e intercambiar datos. Es un formato ligero, basado en texto y legible por humanos que es fácil para las máquinas analizar y generar. En SQLite, JSON puede ser almacenado y consultado de varias maneras diferentes.

Usando la Extensión JSON1

Una forma de almacenar JSON en SQLite es usar la extensión JSON1, que proporciona funciones para codificar y decodificar JSON. Para usar la extensión JSON1, primero necesitas habilitarla ejecutando el siguiente comando:

SELECT load_extension('/path/to/json1/extension');

Una vez que la extensión está habilitada, puedes crear una tabla que tenga una columna de tipo JSON usando la siguiente declaración SQL:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data JSON
);

Insertando Datos JSON

Para insertar un valor JSON en esta tabla, puedes usar la función json_encode proporcionada por la extensión JSON1, así:

INSERT INTO users (id, data) VALUES (1, json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
}));

Consultando Datos JSON

Para consultar esta tabla, puedes usar la función json_extract, que te permite extraer valores de la columna JSON especificando una ruta JSON. Por ejemplo, para obtener la dirección de email del usuario con id 1, puedes usar la siguiente consulta:

SELECT json_extract(data, '$.email') AS email
FROM users
WHERE id = 1;

Almacenar JSON en BLOB

Otra forma de almacenar y consultar JSON en SQLite es usar el tipo BLOB. En este caso, puedes crear una tabla con una columna BLOB así:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  data BLOB
);

Insertando JSON en una Columna BLOB

Para insertar un valor JSON en esta tabla, puedes usar la función json_encode para codificar el valor como una cadena, y luego usar la función hex para convertirlo a un BLOB, así:

INSERT INTO users (id, data) VALUES (1, hex(json_encode({
  "name": "Alice",
  "age": 25,
  "email": "alice@example.com"
})));

Consultando JSON en una Columna BLOB

Para consultar esta tabla, puedes usar la función JSON_EXTRACT proporcionada por la extensión JSON1, pero necesitas convertir el valor BLOB de vuelta a una cadena usando la función unhex, así:

SELECT json_extract(unhex(data), '$.email') AS email
FROM users
WHERE id = 1;

Resumen

En conclusión, SQLite proporciona dos formas de almacenar y consultar valores JSON: usando la extensión JSON1 y usando el tipo BLOB. Ambos enfoques tienen sus propias ventajas y desventajas, y cuál eliges dependerá de tus necesidades específicas.

Si necesitas convertir datos JSON a declaraciones SQL INSERT, revisa nuestra herramienta gratuita Conversor JSON a SQL.