JSON a SQL: sentencias INSERT, diseño de tablas y trampas del mapeo de tipos

En resumen: decide qué entidad de negocio representa una fila antes de diseñar las tablas, y después escribe VALUES multifila explícitos dentro de una única transacción; resuelve el nombrado de campos y el mapeo de tipos antes de la importación en lugar de parchearlos en SQL.

JSON describe la forma; SQL también describe las restricciones. Cuando trasladas lo primero a lo segundo, cada restricción que no dejaste escrita vuelve más tarde como dato sucio.

1. Decide primero: una tabla o un registro por fila del JSON

Mira la capa más externa de los datos antes de diseñar ninguna tabla. Si te equivocas en esta decisión, cada INSERT posterior habrá que reescribirlo.

Solo hay un criterio: ¿qué entidad de negocio representa una fila? Si una fila es un pedido, las líneas de pedido deben vivir en su propia tabla. Si una fila es un usuario, las etiquetas pertenecen a una tabla hija o a una columna JSON, no a una cadena unida por comas.

2. De un array a INSERT: la plantilla ejecutable más pequeña

La forma más segura siempre enumera los nombres de columna explícitamente y nunca depende del orden de las columnas:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Tres detalles importan:

• Escribe todos los nombres de columna. Cuando el esquema gane más tarde una columna, nada se desplazará al campo equivocado.
• VALUES multifila funciona en SQLite, PostgreSQL y MySQL, y ahorra un gran número de idas y vueltas en comparación con un bucle de inserts de una sola fila.
• NULL es una palabra clave; no la entrecomilles. Escribir 'NULL' almacena una cadena de cuatro caracteres.

Si las claves del JSON no coinciden con los nombres de columna (userId frente a user_id), añade una capa de mapeo. No lo adivines con COALESCE dentro del SQL. Corregir los nombres de campo en la capa de datos es mucho más barato que parchearlos en las consultas.

3. Objetos anidados: una tabla principal más una tabla hija

Los pedidos son el caso clásico de uno a muchos:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

Esto corresponde al JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Puntos clave:

• La longitud del array items determina el número de filas hijas; la tabla principal recibe exactamente una fila.
• Almacena el dinero como céntimos enteros (total_cents), nunca como FLOAT.
• Da a la tabla hija una clave foránea order_id y confirma ambos inserts en una transacción, de lo contrario el padre puede existir mientras faltan sus líneas.
• Si los datos son de solo lectura y nadie filtra por línea de pedido, una columna JSON es aceptable (jsonb de PostgreSQL, JSON de MySQL, o TEXT de SQLite con las funciones json1), pero acepta el coste: sin indexación y consultas incómodas.

Aplana los objetos anidados fijos como user.address en columnas (address_city, address_zip). Divide en una tabla solo cuando el anidamiento pueda seguir creciendo; dividir una forma fija solo añade JOINs.

4. NULL frente a campos ausentes: SQL no tiene "clave ausente"

JSON permite que una clave esté completamente ausente. Las columnas de SQL son fijas. Tras una importación, ambos casos se ven idénticos —NULL— aunque pueden significar cosas distintas:

Dos errores habituales:

1. Usar una cadena vacía en lugar de NULL. Más adelante, WHERE email IS NULL pierde esas filas, y tu métrica de "usuarios sin correo" se queda corta en silencio.
2. Comprobar con col = NULL. En la lógica de tres valores de SQL, NULL = NULL se evalúa como NULL, no como TRUE. Debes escribir IS NULL:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

Si necesitas preservar si una clave estaba presente o no, añade una columna marcadora booleana en vez de inferirlo a partir de NULL. El contrato de la API también debería decirlo; consulta Diseño de API JSON y nombrado de campos.

5. Comillas y escapado: nunca concatenes cadenas SQL

Las cadenas JSON pueden contener comillas simples, comillas dobles, barras invertidas, saltos de línea e incluso \u0000. Con cadenas construidas a mano, un escape olvidado desplaza el resto de la sentencia y, en el peor caso, se convierte en inyección SQL.

En SQL estándar, una comilla simple se escapa duplicándola:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

El código de producción debería usar vinculación de parámetros y dejar que el driver gestione el escapado y los tipos:

复制// PostgreSQL usa $1/$2, MySQL usa ?; este ejemplo usa node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Escapa a mano solo cuando debas exportar un archivo .sql, y entonces como mínimo:

• sustituye ' por '';
• si se preservan saltos de línea, confirma que la base de datos de destino acepta literales de cadena multilínea;
• elimina \u0000, ya que la mayoría de las bases de datos rechazan NUL dentro de cadenas;
• MySQL trata la barra invertida como carácter de escape por defecto, así que \ debe convertirse en \\, o cambia al modo estándar.

Las comillas dobles de JSON no necesitan escaparse dentro de una cadena SQL entrecomillada con comillas simples. Añadir una capa extra de escapado es cómo acaban barras invertidas perdidas en tus datos.

6. Mapeo de tipos: dónde aterrizan los seis valores JSON en SQL

JSON solo tiene string, number, boolean, null, object y array. SQL tiene un sistema de tipos mucho más fino. Los errores suelen ocurrir en precisión y semántica, no en sintaxis:

Los booleanos esconden el error más sutil: false en JSON se convierte en 0 en TINYINT(1) de MySQL, las idas y vueltas parecen correctas y nadie se da cuenta. Pero en el momento en que algún código hace if (value) sobre la cadena "false", se evalúa como verdadero. Los scripts de conversión deberían llamar a Boolean(value) explícitamente.

JSONB de PostgreSQL analiza y deduplica las claves, conservando la última aparición, lo que encaja con la consulta; JSON preserva el texto original y el orden de las claves. El JSON de SQLite es solo TEXT más un conjunto de funciones, sin una restricción de tipo real. Antes de elegir, pregúntate si esta tabla necesita filtrar por campos dentro del JSON.

7. Inserts masivos y transacciones: agrupa, confirma, hazlo reejecutable

A medida que crece el volumen, tres cosas deciden si una importación sigue siendo controlable:

1. Agrupa. Un bucle de N sentencias INSERT de una sola fila cuesta N idas y vueltas de red. Combina de 500 a 1000 filas en un único VALUES multfila, o usa el cargador masivo propio de la base de datos (COPY de PostgreSQL, LOAD DATA de MySQL, inserts multfila dentro de una transacción de SQLite).
2. Usa una transacción. Todo o nada, para que una tabla a medio cargar nunca sobreviva a un error:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Hazlo idempotente. Los scripts de importación se vuelven a ejecutar constantemente. Usa un upsert en lugar de borrar y luego insertar:

ON CONFLICT requiere una restricción única o una clave primaria, de lo contrario nunca se dispara. Cuando no hay clave natural, crea un índice único sobre una clave de negocio como email. Cuando no hay ninguna clave, añade un id estable en el lado del JSON; nunca confíes en un id de autoincremento para la idempotencia, porque una reejecución insertará duplicados.

8. Diferencias entre dialectos de un vistazo

Para que un único archivo SQL funcione en los tres, evita al menos ON CONFLICT, los acentos graves y SERIAL. En proyectos multiplataforma, genera por separado el esquema y las sentencias de upsert; no esperes que un solo script sirva para todo.

9. Tres comprobaciones puntuales tras la importación

Antes de borrar el archivo de origen, verifica tres cosas:

1. Recuento de filas. SELECT COUNT(*) FROM users; debería ser igual a la longitud del array JSON. Una fila que falta normalmente significa que se truncó el último registro.
2. La columna con mayor tasa de nulos. Eso apunta a nombres de clave inconsistentes o anidamiento no expandido. Vuelve atrás y comprueba si el origen usa tanto userId como user_id.
3. Dinero e identificadores largos. Toma los valores máximo y mínimo y confirma que ni la conversión de coma flotante ni la de enteros los truncaron.

Si primero necesitas entender la forma tabular, usa el enfoque de aplanado de Conversión de JSON, CSV y datos tabulares. Si el público son colegas que solo quieren leer los datos, las cuatro formas de convertir JSON a Excel dan menos trabajo que montar una base de datos.

¿Por qué los números pierden precisión tras importar JSON a SQL?

Los enteros por encima de 2^53 ya han perdido precisión durante el JSON.parse de JavaScript, y escribirlos en BIGINT no puede restaurarla. Esos ids deberían ser cadenas en JSON y convertirse a BIGINT o TEXT durante la importación.

¿Qué debo usar como clave primaria cuando el JSON no tiene id?

Deja que la base de datos genere una, o crea un UUID estable antes de importar. Nunca uses el índice del array como id: una reejecución reordena los datos y apunta las filas equivocadas a los registros equivocados. Un índice único sobre una clave de negocio (correo, número de pedido) también habilita los upserts.

¿Qué hago cuando un INSERT masivo choca con una clave única?

Sustituye el INSERT simple por el upsert correspondiente a tu dialecto, y confirma que la columna en conflicto tiene una restricción única. Borrar primero e insertar después también funciona, pero debe ocurrir dentro de una transacción y aceptas una breve ventana sin filas.

¿Los arrays anidados siempre necesitan una tabla hija?

No. Para visualización de solo lectura sin consultar por subelemento, una columna JSON es más simple. En cuanto necesites filtrar o agregar por etiqueta o SKU, una tabla hija con índices es la elección sostenible. El factor decisivo es cómo consultas, no cómo está formada la estructura.