Funciones JSON útiles en SQLite3

En este artículo te quiero compartir la manera en la que yo uso las funciones json_object y json_group_array.

Siento que estas 2 funciones son muy útiles para agrupar datos.

Le he encontrado 2 usos que permiten traer más filas en una sola fila. Esto también puede conocerse como obtener varias filas de subconsulta en una columna o traer muchas filas agrupadas en un campo en consulta SQL, similar a lo que hicimos con PostgreSQL

Usando json_object

La función json_object de SQLite3 te permite codificar un objeto JSON, por ejemplo:

SELECT
    entradas.id,
    fecha,
    id_usuario,
    json_object('id', usuarios.id, 'nombre', usuarios.nombre) AS usuarioComoJson
FROM
    entradas
    INNER JOIN usuarios ON usuarios.id = entradas.id_usuario

Fíjate sobre todo en la invocación a json_object:

    json_object('id', usuarios.id, 'nombre', usuarios.nombre) AS usuarioComoJson

Eso me va a devolver todos los datos del usuario en una sola fila. Esto es perfecto, ya que luego en el lado del cliente solo debo decodificar el JSON y puedo usar el objeto.

La salida sería:

1	2025-10-06T18:36:59	1	{"id":1,"nombre":"parzibyte"}
2	2025-10-06T18:37:04	1	{"id":1,"nombre":"parzibyte"}
3	2025-10-06T19:11:17	1	{"id":1,"nombre":"parzibyte"}
4	2025-10-06T19:16:22	1	{"id":1,"nombre":"parzibyte"}
5	2025-10-06T19:26:13	1	{"id":1,"nombre":"parzibyte"}
6	2025-10-06T19:26:53	1	{"id":1,"nombre":"parzibyte"}

Además, si añado una propiedad, por ejemplo, correo solo debería modificar así:

    json_object('id', usuarios.id, 'nombre', usuarios.nombre, 'correo', usuarios.correo) AS usuarioComoJson

Si esta función no existiera también sería posible hacerlo pero no quedaría agrupado de una manera tan bonita, tendría que hacer algo así:

SELECT
    entradas.id,
    fecha,
    usuarios.id as id_usuario,
    usuarios.nombre
FROM
    entradas
    INNER JOIN usuarios ON usuarios.id = entradas.id_usuario

Y el usuario no vendría agrupado, además de que los campos podrían colisionar y tendría que ser más específico. Además, si luego quiero traer también otra tabla, sería así con JSON:

SELECT
    entradas.id,
    fecha,
    id_usuario,
    json_object('id', usuarios.id, 'nombre', usuarios.nombre) AS usuarioComoJson
    json_object('id', otra_tabla.id, 'otro_campo', otra_tabla.otro_campo) AS otroCampoComoJson
FROM
    entradas
    INNER JOIN usuarios ON usuarios.id = entradas.id_usuario
    INNER JOIN otra_tabla ON otra_tabla.id = entradas.id_otra_tabla

Desconozco si los maestros de SQL conocen otra técnica pero para mí esta función me facilita mucho las cosas, sobre todo en conjunto con la siguiente función.

Usando json_group_array

Esta es otra función que me gusta mucho porque no solamente permite agrupar un objeto, permite agrupar todo un arreglo de filas en un solo campo.

Veamos un ejemplo:

SELECT
    entradas.id,
    fecha,
    id_usuario,
    json_object('id', usuarios.id, 'nombre', usuarios.nombre) AS usuarioComoJson,
    (
        select
            json_group_array(
                json_object(
                    'id',
                    productos_entradas.id,
                    'nombre',
                    productos.nombre,
                    'cantidad',
                    productos_entradas.cantidad
                )
            )
        from
            productos_entradas
            inner join productos ON productos.id = productos_entradas.id_producto
        where
            productos_entradas.id_entrada = entradas.id
    ) as productosComoJson
FROM
    entradas
    INNER JOIN usuarios ON usuarios.id = entradas.id_usuario

Centrémonos de nuevo en la función json_group_array:

select
    json_group_array(
        json_object(
            'id',
            productos_entradas.id,
            'nombre',
            productos.nombre,
            'cantidad',
            productos_entradas.cantidad
        )
    )
from
    productos_entradas
    inner join productos ON productos.id = productos_entradas.id_producto
where
    productos_entradas.id_entrada = entradas.id

Esto nos permite agrupar cualquier cantidad de filas en una sola propiedad usando json_group_array y json_object. Cada objeto creado con json_object será colocado en el arreglo creado de json_group_array

La salida al ejecutar la consulta es:

1	2025-10-06T18:36:59	1	{"id":1,"nombre":"parzibyte"}	[{"id":1,"nombre":"KG pollo crudo","cantidad":1.0}]
2	2025-10-06T18:37:04	1	{"id":1,"nombre":"parzibyte"}	[{"id":2,"nombre":"KG pollo crudo","cantidad":1.0},{"id":3,"nombre":"1","cantidad":1.0}]
3	2025-10-06T19:11:17	1	{"id":1,"nombre":"parzibyte"}	[{"id":4,"nombre":"KG papa","cantidad":4.0},{"id":5,"nombre":"Rebanada jamón","cantidad":3.0},{"id":6,"nombre":"KG zanahoria","cantidad":2.0}]
4	2025-10-06T19:16:22	1	{"id":1,"nombre":"parzibyte"}	[{"id":7,"nombre":"KG papa","cantidad":10.0},{"id":8,"nombre":"KG zanahoria","cantidad":0.5}]
5	2025-10-06T19:26:13	1	{"id":1,"nombre":"parzibyte"}	[{"id":9,"nombre":"KG papa","cantidad":13.0},{"id":10,"nombre":"KG zanahoria","cantidad":0.5}]
6	2025-10-06T19:26:53	1	{"id":1,"nombre":"parzibyte"}	[{"id":11,"nombre":"KG papa","cantidad":1.0},{"id":12,"nombre":"Rebanada jamón","cantidad":1.0},{"id":13,"nombre":"KG zanahoria","cantidad":1.0}]

Como puedes ver tengo varias filas de la tabla productos_entradas en una sola celda de mi consulta principal con SQLite3.

También podemos anidar las funciones.

Sobre el rendimiento

Lo mostrado aquí debe ser un poco pesado, pues se van a estar creando objetos JSON por cada fila. Dependiendo de la cantidad de filas esto puede afectar al rendimiento.

Yo lo hago de esta manera porque debo mostrar todos los productos que se registraron en una entrada.

Otra opción sería consultar todas las entradas, recorrerlas y por cada una de ellas asignarle la propiedad productos que sería el resultado de ejecutar otra consulta, pero ahí ejecutaríamos una consulta por cada valor existente en entradas así que yo prefiero esta opción ya que la paginación y filtros hacen que las filas devueltas sean pocas.