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.

Dan McGhan · Accepted Answer · 2017-08-29 14:23:27Z

4

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?

answered Aug 29, 2017 at 14:23

Dan McGhan

4,6691 gold badge14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joseph Over a year ago

I will look into it closely. Looks like it gives me what I am looking for. The ORMs I idod look are Sworm (github.com/featurist/sworm), Typeorm (github.com/typeorm/typeorm)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related