0
187
Views¿Cómo migrar una tabla JSON que contiene una matriz a SQL?
Soy un desarrollador front-end en prácticas. Realmente nunca me gustó el back-end, así que sé muy poco sobre SQL.
Pero aquí estamos, en uno de esos tiempos de aventuras en los que necesito hacer el backend porque también me asignaron. Bueno, tenemos un sistema interno para rastrear algunos datos sobre nosotros mismos que estamos reconstruyendo. El sistema anterior usaba un archivo JSON para almacenar sus datos, en un formato como este:
{ "id": 123, "userId": 522, "effortDate": "2020-06-05", "data": [ { "projectId": 14, "value": 7 }, { "projectId": 23, "value": 3 } ], "updatedAt": "2020-06-08T10:13:11-03:00", "createdAt": "2020-06-08T10:13:11-03:00" }Nos estamos moviendo a una base de datos basada en SQL. Entonces, con mi comprensión básica de cómo funcionan las bases de datos relacionales,
He construido este modelo:
.
El problema es que, al menos por ahora, estamos usando el mismo front-end para la aplicación, por lo que necesito que los datos se analicen en el mismo formato que tenían entonces, cuando solo era un JSON. Y para la operación básica del sistema, necesita requerir todos los datos del ahora, dos tablas, todo el tiempo.
Mi primera idea fue mezclarlos, ya sabes, a la fuerza, con dos SELECT diferentes. Lo cual, supongo, no es tan malo (tal vez porque funciona) , excepto que estamos tratando con miles de entradas, así que tengo miedo de que las cosas se vuelvan lentas. Aquí está el código que usé para analizarlo:
public function getAllEfforts() { /* Get all data from `effort` */ $effortQuery = "SELECT id, userId, effortDate, createdAt, updatedAt FROM efforts;"; $efforts = $this->pdo ->query($effortQuery) ->fetchAll(PDO::FETCH_ASSOC); /* Get all data from `effort_data` */ $effortDataQuery = "SELECT id, effortId, projectId, value FROM efforts_data;"; $effortsData = $this->pdo ->query($effortDataQuery) ->fetchAll(PDO::FETCH_ASSOC); /* Since, `effort` doesn't have a data field, let's define the array wished here */ foreach($efforts as &$effortsUnity) { $effortsUnity['data'] = []; } /* Push the stuff from `effort_data` to the just created data array */ foreach($effortsData as &$effortDataUnity) { $effortIndex = ($effortDataUnity['effortId'] - 1); unset($effortDataUnity['id']); unset($effortDataUnity['effortId']); array_push( $efforts[$effortIndex]['data'], $effortDataUnity ); } return $efforts; }Mi pregunta es: ¿hay alguna forma de hacerlo usando solo SQL? O cualquier otra forma óptima de hacerlo además de usar MongoDB u otra base de datos basada en JSON que no es posible en este momento.
Lo siento, si no fui lo suficientemente claro, es mi primera pregunta aquí, estoy dispuesto a aclarar cualquier pregunta.
Muchas gracias.
2 answers
Answer question0
Puede aprovechar las funciones JSON de MySQL para generar una estructura JSON anidada directamente desde la base de datos.
Considerar:
SELECT id, userId, effortDate, createdAt, updatedAt, ( SELECT JSON_ARRAYAGG( JSON_OBJECT('projectId', ed.projectId, 'value', ed.value) ) FROM efforts_data ed WHERE ed.effortId = e.id ) AS data FROM efforts Esto le da una fila por efforts , con una columna llamada data que contiene una carga útil JSON hecha de una matriz de objetos provenientes de la tabla efforts_data .
Puede dar un paso adelante y rellenar cada fila en un solo objeto, si eso es lo que desea:
SELECT JSON_OBJECT( 'id', id, 'userId', userId, 'effortDate', effortDate, 'createdAt', createdAt, 'updatedAt', updatedAt, 'data', ( SELECT JSON_ARRAYAGG( JSON_OBJECT('projectId', ed.projectId, 'value', ed.value) ) FROM efforts_data ed WHERE ed.effortId = e.id ) ) res FROM efforts En MySQL < 5.7.22 o MariaDB < 10.5.0, donde JSON_ARRAYAGG() aún no está disponible, una solución alternativa es GROUP_CONCAT() y la concatenación de cadenas. Básicamente, reescribirías esto:
SELECT JSON_ARRAYAGG( JSON_OBJECT('projectId', ed.projectId, 'value', ed.value) ) FROM efforts_data ed WHERE ed.effortId = e.idComo:
SELECT CONCAT( '[', GROUP_CONCAT(JSON_OBJECT('projectId', ed.projectId, 'value', ed.value)), ']' ) FROM efforts_data ed WHERE ed.effortId = e.id0
Mi pregunta es: ¿hay alguna forma de hacerlo usando solo SQL?
Sí, es por eso que tienes la relación entre id de efforts id efforts_data effortId :
SELECT e.id, e.userId, e.effortDate, e.createdAt, e.updatedAt, ed.id, ed.effortId, ed.projectId, ed.value FROM efforts AS e, efforts_data AS ed WHERE e.id = ed.effortId