How to form and get nested JSON object from query using oracledb using NodeJS with ExpressJS

Question

I have to query a set of data which I expected to be nested to have itt more meaningful. When using ExpressJS with oracledb framework to query data as below,

oracledb.getConnection(getConnectAttr, function(err,connection){
    if(err){
      res.set('content-type','application/json');
      res.status(500).send(JSON.stringify({
        status:500,
        message:"Error connection to DB",
        detailed_message:err.message
      }));
      return;
    }
      connection.execute("select * from REGISTRATION_MASTER", {}, {outFormat : oracledb.OBJECT
      },function(err, result){
        if(err){
          res.set('content-type','application/json');
      res.status(500).send(JSON.stringify({
        status:500,
        message:"Error connection the REGISTRATION",
        detailed_message:err.message
      }));
        }
        else{
          res.contentType('application/json').status(200);
          res.send(result.rows);
        }

        //Release the connection
        connection.release(
          function(err){
            if(err){
              console.error(err.message);
            }
            else{
              console.log("GET/comments : connection released")
            }
          });
      });

  });

I used to query and the query result will be something like,

[   
    {
        "REG_ID": 1,
        "REG_TEXT": "User Name",
        "REG_VALUE": null,
        "REG_IS_REQUIRED": "true",
        "REG_TYPE": "text"
    },
    {
        "REG_ID": 2,
        "REG_TEXT": "Password",
        "REG_VALUE": null,
        "REG_IS_REQUIRED": "true",
        "REG_TYPE": "password"
    },
    {
        "REG_ID": 3,
        "REG_TEXT": "First Name",
        "REG_VALUE": null,
        "REG_IS_REQUIRED": "true",
        "REG_TYPE": "text"
    }   
]

I actually need to form a json by querying in such a way that the output should be like

{
  "REG_FIELDS": [{
    "REG_ID": 1, "REG_TEXT": "User Name", "REG_VALUE": "", "REG_IS_REQUIRED": "true",
    "REG_TYPE": "text"
  }, {
    "REG_ID": 2, "REG_TEXT": "Password", "REG_VALUE": "",
    "REG_IS_REQUIRED": "true", "REG_TYPE": "password"
  }, {
    "REG_ID": 3, "REG_TEXT": "First Name",
    "REG_VALUE": "", "REG_IS_REQUIRED": "true", "REG_TYPE": "text"
  }, ],
  "MAINHEADING": "CONSUMER SIGNUP",
  "SUBHEADER": "ACCOUNT - CONSUMER - SIGN UP",
  "IS_ACTIVE": "TRUE"
};

I am looking for such output with nested values. My requirement is going to have more nested values. I am looking for a start point.

I also tried using ORMs with ExpressJS but each has their own drawback when looking to use ExpressJS with Oracle. Thanks.

Answer 1

You can execute multiple queries and then use the results to build a more complex data structure that you send out. Have a look at this: https://jsao.io/2015/07/relational-to-json-with-node-js/

Also, note that you shouldn't send database errors out to the client. This can open you up to SQL injection as it allows people to learn more about your database than they should. Send the client a generic message and log the actual error so you can debug it.

I'm curious, what ORMs did you try and what were the drawbacks?