Me gustaría exportar los datos de mi base de datos al formato json a continuación, ya lo había intentado pero solo puedo mostrar 1 tabla y no toda con referencia.
CREATE TABLE generic ( id INTEGER NOT NULL PRIMARY KEY, errorCode INT, errorMsg TEXT, PRIMARY KEY (id) ); CREATE TABLE generic_data ( generic_id TEXT, id INT, name TEXT, ovpn TEXT, category TEXT, PRIMARY KEY (id), FOREIGN KEY (generic_id) REFERENCES generic(id) ); CREATE TABLE generic_data_children ( generic_data_id INT, id INT, name TEXT, config TEXT, PRIMARY KEY (id), FOREIGN KEY (generic_data_id) REFERENCES generic_data(id) );Aquí está la información de mi base de datos con las columnas de la tabla como referencia
Formato Json esperado:
{ "data": [{ "children": [{ "id": 1, "name": "Default", "config": "config1" }, { "id": 2, "name": "sample", "config": "config2" }, { "id": 3, "name": "sample2", "config": "config3" }, { "id": 4, "name": "testnetwork", "config": "config4" }, { "id": 5, "name": "sample5", "config": "config5" }], "id": 1, "name": "PH-Philippines 1", "ovpn": "testovpn", "category": "Private" }], "errorCode": 0, "errorMsg": "" }Puede usar JSON_ARRAY() , GROUP_CONCAT() y JSON_OBJECT() juntas como
SELECT JSON_PRETTY( REPLACE( REPLACE( REPLACE( JSON_OBJECT( 'errorCode', g.errorCode, 'errorMsg', COALESCE(g.errorMsg,""), 'data',JSON_ARRAY( (SELECT JSON_OBJECT('id',gd.id, 'name',gd.name, 'ovpn',gd.ovpn, 'children', JSON_ARRAY( GROUP_CONCAT( JSON_OBJECT('name',gdc.name, 'id',gdc.id, 'config',gdc.config) ) ) ) FROM generic_data_children gdc LEFT JOIN generic_data AS gd ON gd.id = gdc.generic_data_id WHERE generic_data_id = g.id ) ) ) ,'\\"','"') ,'"{','{') ,'}"','}') ) AS 'New JSON' FROM generic g El uso de las funciones REPLACE() es necesario al final para formatear las comillas dobles.
Por cierto, elimine la repetición PRIMARY KEY para la primera tabla y convierta el tipo de datos de la columna generic_id a INT dentro de la segunda tabla.