How to return a nested json in node.js mysql in a single query

Question

I'm trying to create an api that will return a nested json, coming from two related tables student and studentSubjects

[{
   id:"1",
   name: "John",
   subjects: [{
                id:"1",
                subject: "Math"
              },
              {
                id:"2",
                subject: "English"
              }
             ]
 },
 {
   id:"2",
   name: "Peter",
   subjects: [{
                id:"1",
                subject: "Math"
              },
              {
                id:"2",
                subject: "English"
              }
             ]
}]

My code looks like this:

this.get = function(res){
    db.acquire(function(err, con){                  
        con.query('SELECT * FROM students', function(err, results){ 

            if (err){                   
                res.send({status: 0, message: 'Database error'});
            }else{                  
                res.send({status: 1, data: results});                   
            }                                       
        })  
        con.release()           
    })
}

I know the query should have joins, but it only returns single row. I tried also to make a loop, it won't work because its async

Thanks for your help!!

Answer 1

MySQL 5.7+ has a JSON datatype that you can leverage for your "subjects" field. Here's a great tutorial on how to use that:

https://www.sitepoint.com/use-json-data-fields-mysql-databases/

Answer 2

You cannot create a nested JSON from a MySQL query because it will always return a flat result.

Anyway, to create a nested JSON you should create multiple queries and insert the corresponding array object where needed.

You should really consider using Promises for creating nested queries because it will allow you to make asynchronous operations back to back. Below code will also close the connection if an error occurs in any of the queries.

PS: I explained each step in the comments in the code below

Imagine having a database called 'School' and three tables called 'Student', 'Subject' and 'Link_student_subject'.

// Instantiate mysql datase variables
const mysql = require( 'mysql' );
const config = {
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'school'
  }
var connection;

// Instantiate express routing variables 
const express = require('express');
const router = express.Router();
module.exports = router;

// Wrapper class for MySQL client
// - Constructor creates MySQL connection
// - Connection opened when query is done
// - Promise is resolved when executing
// - Promise returns reject in case of error
// - If promise resolved rows will be the result
class Database {
    constructor( config ) {
        this.connection = mysql.createConnection( config );
    }
    query( sql, args ) {
        return new Promise( ( resolve, reject ) => {
            this.connection.query( sql, args, ( err, rows ) => {
                if ( err )
                    return reject( err );
                resolve( rows );
            } );
        } );
    }
    close() {
        return new Promise( ( resolve, reject ) => {
            this.connection.end( err => {
                if ( err )
                    return reject( err );
                resolve();
            } );
        } );
    }
}

// Function that will execute a query
// - In case of an error: ensure connection is always closed
// - In case of succes: return result and close connection afterwards
Database.execute = function( config, callback ) {
  const database = new Database( config );
  return callback( database ).then(
      result => database.close().then( () => result ),
      err => database.close().then( () => { throw err; } )
  );
};

// Instantiate Database
var database = new Database(config);

// Express routing
router.get('/students', function (req, res) {

  // Variables - Rows from Students & Subjects & Link_student_subject
  let rows_Students, rows_Subjects, rows_Link_Student_Subject;

  // Create a Promise chain by
  // executing two or more asynchronous operations back to back, 
  // where each subsequent operation starts when the previous operation succeeds, 
  // with the result from the previous step
  Database.execute( config,
      database => database.query( "select a.*, null as subjects from student a" )
      .then( rows => {
        rows_Students = rows;
        return database.query( "select * from subject" )
      } )
      .then( rows => {
        rows_Subjects = rows;
        return database.query( "select * from link_student_subject" )
      } )
      .then( rows => {
        rows_Link_Student_Subject = rows;
      } )
  ).then( () => {
      // Create your nested student JSON by looping on Students
      // and inserting the corresponding Subjects array
      for (let i = 0; i < rows_Students.length; i++) {
        let arraySubjects = [];
        for (let x = 0; x < rows_Link_Student_Subject.length; x++) {
            if(rows_Students[i].id == rows_Link_Student_Subject[x].id_student){
                arraySubjects.push(searchObjInArray(rows_Subjects, "id", rows_Link_Student_Subject[x].id_subject));
            }
        }
        rows_Students[i].subjects = arraySubjects;
      }
      res.send(JSON.stringify(rows_Students));
  } ).catch( err => {
      // handle the error
      res.send(err);
  });

});

// Function - search if object in array has a value and return that object
function searchObjInArray(array, arrayProp, searchVal){
    let result = null;
    let obj = array.find((o, i) => {
        if (o[arrayProp] == searchVal) {
        result = array[i];
        return true; // stop find function
        }
    });
    return result;
}

If you run this code with node and go to "127.0.0.1/students" it will return exactly the same JSON as in your question.

All credits and extra info on MySQL and promises - https://codeburst.io/node-js-mysql-and-promises-4c3be599909b