Aprendiendo lo básico: Cómo usar JSON en SQLite

En esta guía, exploramos una fascinante intersección entre dos tecnologías populares: JSON y SQLite. Saber cómo usar JSON con SQLite es importante en el desarrollo de software moderno, especialmente cuando se trata de estructuras de datos complejas que pueden no encajar completamente en una estructura tabulada. Ya seas un desarrollador experimentado o un principiante ansioso por ampliar tus conocimientos, este tutorial te ayudará a comenzar a aprender cómo usar JSON en la base de datos SQLite.

¡Empecemos!

Potenciar la Gestión de Datos de Aplicaciones Modernas con SQLite

SQLite como el motor de base de datos más ampliamente implementado y utilizado es una base de datos amigable que no requiere ninguna configuración complicada ni conexión con el servidor. SQLite es sencillo y adaptable a diversas aplicaciones, lo que lo ha convertido en la opción preferida en el desarrollo de software. SQLite tiene una pequeña huella binaria, generalmente menos de 1 MB, lo que significa que es más ligero que otras bases de datos. Además, SQLite cumple completamente con los principios ACID.

Otra característica única de SQLite es que está bien adaptado para aplicaciones individuales y dispositivos conectados a Internet, como dispositivos inteligentes para el hogar, que forman parte de las cosas de Internet (IoT). Además, a pesar de su simplicidad, SQLite tiene un fuerte control sobre el lenguaje SQL estándar. Puede manejar cosas como transacciones, subconsultas y disparadores. Por lo tanto, SQLite es fácil de usar, pero aún así bastante potente.

La capacidad de SQLite va más allá de solo el almacenamiento de datos simple. SQLite es eficiente y amigable con el usuario, con características como la búsqueda de texto completo y el soporte de blob. SQLite también proporciona un mecanismo de extensión para funcionalidades adicionales, lo que lo convierte en una herramienta adaptable en el ecosistema de software moderno.

Dato curioso: ¿Sabías que, aunque muchas personas pronuncian SQLite como ‘S-Q-Lite’ (secuela-ligera), su creador, Richard Hipp, en realidad lo quiso pronunciar como ‘S-Q-L-ite’ (es-que-el-lite) como un mineral, enfatizando su naturaleza robusta pero ligera?

Por qué SQLite sobresale en el ámbito de las bases de datos relacionales modernas

SQLite es una solución ideal para escenarios en los que los bases de datos a gran escala de cliente-servidor podrían ser excesivos, ya que es ligero y sin servidor. Debido a que SQLite es autónomo, no depende de ninguna dependencia externa, lo que lo hace muy confiable. Las bases de datos SQLite son portables a través de diferentes sistemas de archivos y arquitecturas, por lo que la migración de datos en la base de datos SQLite es sin esfuerzo.

Los casos de uso típicos de SQLite se extienden a través de una variedad de dominios, ya que es el motor de base de datos más ampliamente implementado en existencia. Por ejemplo, SQLite es una opción estándar para la persistencia local en aplicaciones, especialmente en aplicaciones móviles. SQLite también se utiliza ampliamente para análisis de datos y pruebas, donde su claridad y potencia son una combinación ganadora. Por último, SQLite es una elección ideal para el almacenamiento de datos de sitios web, donde puede gestionar datos de usuarios, contenido del sitio y más.

El rendimiento de SQLite es impresionante, con velocidad que a menudo supera a otras bases de datos famosas para la mayoría de las operaciones comunes.

¿Por qué las capacidades de manejo de JSON de SQLite lo convierten en una opción excepcional para la gestión de datos modernos?

Administrar datos no estructurados de manera eficiente es un desafío que enfrentan muchos desarrolladores. Ahí es donde entra JSON. Como un formato de datos flexible y sin esquema, JSON es útil para manejar datos que no encajan perfectamente en una estructura tabular.

Al almacenar datos JSON en SQLite, puedes aprovechar las potentes capacidades de consulta de SQLite para extraer y manipular tus datos JSON de manera eficiente. La belleza de esta combinación radica en el hecho de que SQLite viene con funciones integradas para manejar datos JSON con simplicidad. Además, la popularidad de JSON como formato de intercambio de datos y su portabilidad significa que los datos JSON almacenados en SQLite pueden compartirse, migrarse o exportarse fácilmente a diferentes sistemas.

El soporte de JSON en SQLite ha madurado con el tiempo. Fue introducido por primera vez como una extensión en la versión 3.9.0, lanzada en 2015, pero las versiones posteriores vienen con soporte integrado para JSON. SQLite te permite guardar y recuperar datos JSON utilizando una columna de tipo TEXT y un conjunto de funciones JSON, como json(), json_extract(), json_object(), y json_array().

Entendiendo las poderosas funciones de SQLite para la consulta de JSON

SQLite gestiona y manipula datos JSON utilizando funciones JSON. A continuación, se presentan las 10 principales funciones JSON en SQLite, listadas como referencia, y el caso de uso de cada una se ilustrará mediante una consulta SQL sencilla en la siguiente sección.

1. json(): Esta función verifica si una cadena es un JSON válido. Si lo es, la función devuelve el mismo JSON. Si no lo es, devuelve NULL.
2. json_extract(): Esta función extrae un objeto de una cadena JSON utilizando una ruta.
3. json_array(): Esta función crea un arreglo JSON.
4. json_array_length(): Esta función devuelve la longitud del arreglo JSON.
5. json_insert(): Esta función inserta un valor JSON en una cadena JSON.
6. json_object(): Esta función crea un objeto JSON.
7. json_remove(): Esta función elimina una propiedad de la cadena JSON.
8. json_replace(): Esta función reemplaza un valor en una cadena JSON.
9. json_type(): Esta función devuelve el tipo del valor JSON (como INTEGER, REAL, NULL, TRUE, FALSE, TEXT y BLOB).
10. json_valid(): Esta función verifica si una cadena es un JSON válido.

Serie práctica de ejemplos para comprender las funciones de codificación y decodificación JSON en SQLite

En esta sección, hemos proporcionado ejemplos mínimos y una breve explicación para cada una de las funciones JSON que enumeramos en la sección anterior. Utilizamos un ejemplo de datos JSON del generador de Dadroit JSON. Aquí está el JSON original para brindarle contexto.

La función json() en SQLite

Esta consulta convierte el texto JSON en un objeto JSON.

SELECT
    json ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}' ) AS json_object;

El resultado de esta consulta sería así:

La función json_extract() en SQLite

Esta consulta extrae el valor Name del objeto JSON utilizando la ruta.

SELECT json_extract('{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Name') AS movie_name;

El resultado de esta consulta sería así:

La función json_array() en SQLite

Esta consulta crea un nuevo arreglo JSON a partir de las entradas proporcionadas.

SELECT
    json_array ( 1, 2, 3 ) AS array_result;

El resultado sería así:

La función json_type() en SQLite

Esta consulta recupera el tipo de datos del valor Year del objeto JSON.

SELECT
    json_type ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Year' ) AS property_type;

El resultado sería así:

La función json_array_length() en SQLite

Esta consulta cuenta el número de elementos en el arreglo Cast en el objeto JSON.

SELECT
    json_array_length ( '{"Genre":["Comedy","Crime"],"Cast":["Adrian Gratianna","Tani O''Hara","Tessie Delisle"]}', '$.Cast' ) AS array_length;

El resultado sería así:

La función json_object() en SQLite

Esta consulta crea un objeto JSON con los pares clave-valor ID y Name.

SELECT
    json_object ( 'ID', 1, 'Name', 'Forgotten in the Planet' ) AS result;

El resultado sería así:

La función json_insert() en SQLite

Esta consulta inserta la propiedad clave-valor Director en el objeto JSON.

SELECT
    json_insert ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970}', '$.Director', 'Henrie Randell Githens' ) AS insert_movie;

El resultado sería así:

La función json_remove() en SQLite

Esta consulta elimina el par clave-valor Director del objeto JSON.

SELECT
    json_remove ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Director' ) AS result_of_remove;

El resultado sería así:

La función json_replace() en SQLite

Esta consulta reemplaza el Year en el objeto JSON con el nuevo valor 1971.

SELECT
     json_replace ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}', '$.Year', 1971 ) AS result_of_replace;

El resultado sería así:

La función json_valid() en SQLite

Esta consulta verifica si la cadena proporcionada tiene la sintaxis y estructura correctas requeridas para un JSON válido, y devuelve 1 si lo fue y 0 en caso contrario.

SELECT
     json_valid ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Director":"Henrie Randell Githens"}' ) AS result_of_valid;

El resultado sería así:

Ejemplos prácticos de consultas para una consulta SQL mejorada utilizando funciones JSON en SQLite

Ahora que has aprendido sobre lo básico de JSON en SQLite, aquí se te presentan algunos ejemplos más de un flujo de trabajo práctico con datos JSON en la base de datos SQLite, utilizando las funciones JSON mencionadas anteriormente y los datos JSON mencionados como entrada.

Almacenamiento de datos JSON en SQLite con consultas de inserción

Primero, necesitas insertar el JSON en una base de datos SQLite. Vamos a crear una tabla llamada movies con un campo llamado data como campo de texto, ya que puedes almacenar JSON en SQLite en un campo de texto. Usarás este campo data para almacenar y recuperar los valores JSON:

CREATE TABLE movies ( data TEXT );

Luego, insertemos nuestro JSON en el campo data de la tabla movies:

INSERT INTO movies ( data )
 VALUES
     ( '{"ID":1,"Name":"Forgotten in the Planet","Year":1970,"Genre":["Comedy","Crime"],"Director":"Henrie Randell Githens","Cast":["Adrian Gratianna","Tani OHara","Tessie Delisle"],"Runtime":90,"Rate":7.0}' );

Para editar (reemplazar, insertar, eliminar, etc.) JSON en SQLite, puedes usar las funciones json_replace(), json_insert() y json_remove().

La siguiente consulta reemplaza el nombre de la película con el nuevo valor donde el ID es 1:

UPDATE movies
 SET data = json_replace ( data, '$.Name', 'Found in the Universe' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

La siguiente consulta inserta una nueva propiedad como un nuevo campo en los datos JSON almacenados previamente en la fila:

UPDATE movies
 SET data = json_insert ( data, '$.Country', 'USA' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

La siguiente consulta elimina la propiedad Runtime de los datos JSON almacenados previamente en la fila:

UPDATE movies
 SET data = json_remove ( data, '$.Runtime' )
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Extraer datos JSON de SQLite

Para recuperar datos JSON de SQLite, puedes usar la función json_extract() o el operador abreviado ->:

Selecciona el nombre de la película:

SELECT
     json_extract ( data, '$.Name' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

O usando el operador abreviado ->:

SELECT
     data -> '$.Name'
 FROM
     movies
 WHERE
     data -> '$.ID' = 1;

Recupera la lista de géneros:

SELECT
     json_extract ( data, '$.Genre' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Recupera el primer actor de la lista Cast:

SELECT
     json_extract ( data, '$.Cast[0]' )
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Extrae el Year y el Rate:

SELECT
     json_extract ( data, '$.Year' ) AS Year,
     json_extract ( data, '$.Rate' ) AS Rate
 FROM
     movies
 WHERE
     json_extract ( data, '$.ID' ) = 1;

Conclusiones y reflexiones

¡Bien hecho por completar este viaje! Has aprendido cómo el tipo de dato JSON y la base de datos SQLite pueden trabajar juntos. SQLite es una herramienta útil para tener en tu caja de herramientas. Es simple pero potente, y fácil de usar. A pesar de su tamaño pequeño, está lleno de características útiles.

En pocas palabras, SQLite nos permite guardar y recuperar datos JSON utilizando una columna de texto y algunas funciones JSON. Estas funciones nos permiten explorar, analizar y cambiar los datos JSON en nuestra base de datos SQLite. SQLite ofrece muchas herramientas para administrar datos JSON, desde agregar y cambiar datos JSON hasta recuperarlo para varios propósitos. Cubrimos diez funciones principales de JSON en SQLite que facilitan el manejo de datos JSON. Luego, analizamos algunos ejemplos adicionales de consultas SQL utilizando estas funciones JSON en SQLite.

Recuerda: dominar el uso de JSON con SQLite es una habilidad que necesita ser practicada más a fondo. Así que, no seas tímido, sumérgete, experimenta y aprende. Al final, si encontraste útil esta guía, siéntete libre de compartirla. ¡Disfruta de tu viaje de codificación!