CODEWITHSUNDEEP
node.jsjsonoracle-databaseexpress
VersifiXion
VersifiXion

Reputation: 2292

Convert Oracle query output to json (Oracle / NodeJS)

I'm doing an app with React / Express (NodeJS) / Oracle,

I have an Express route that gets datas from an Oracle table,

Here's a part of the code in the route : 

let conn;
  try {
conn = await oracledb.getConnection(config);
const result = await conn.execute("select  JSON_OBJECT ('departement' VALUE departement, 'ufh' VALUE ufh, 'libelle' VALUE libelle, 'nomhopital' VALUE nomhopital, 'typeservice' VALUE typeservice, 'actif' VALUE actif)  from Z_SOUPAP2CARTESITE where actif=1");

res.send(result.rows);
}

But when I go on the route in the browser, the datas have this shape :

[["92","028X362","ABC ACCUEIL URG MEDECINE","ANTOINE BECLERE","ADULTE",1],["92","028X472","ABC URGENCES PEDIATRIQUE","ANTOINE BECLERE","PEDIATRIE",1],["92","014X545","APR ACCEUIL URGENCES ADU","AMBROISE PARE","ADULTE",1]]

and I want this :

[
  {"departement":"92","ufh":"028X362","libelle":"ABC ACCUEIL URG MEDECINE","nomhopital":"ANTOINE BECLERE","typeservice":"ADULTE","actif":1},
  {"departement":"92","ufh":"028X472","libelle":"ABC URGENCES PEDIATRIQUE","nomhopital":"ANTOINE BECLERE","typeservice":"PEDIATRIE","actif":1}
]

Upvotes: 1

Views: 4386

Answers (2)

Abdullah
Abdullah

Reputation: 2993

The below solution works for me:

app.post('/getData', async function (req, res) {
try {
    const result = await connection.execute("SELECT * FORM USER", [], { outFormat: oracledb.OUT_FORMAT_OBJECT });
    // print response in json file: import => const fs = require("fs");
    fs.writeFile("response.json", JSON.stringify(result.rows), err => {
        if (err) throw err;
        console.log('File successfully written to disk');
    });

    res.send({
        data: result.rows,
    });
} catch (err) {
    console.log(err);
}});

Upvotes: 0

Dan McGhan
Dan McGhan

Reputation: 4659

Why are you using JSON_VALUE? The driver returns native JavaScript objects. You could write the query as:

select department "department",
  ufh "ufh",
  libelle "libelle",
  nomhopital "nomhopital",
  typeservice "typeservice"
from Z_SOUPAP2CARTESITE 
where actif=1

In the query above, the double-quoted column aliases are used to control the case of the keys.

By default, the driver returns an array of arrays (no keys). If you want an array of objects, you need to pass an options object to execute that changes the outFormat. See this part of the doc: https://oracle.github.io/node-oracledb/doc/api.html#queryoutputformats

Here's an example from the doc:

const result = await connection.execute(
  `SELECT department_id, department_name
   FROM departments
   WHERE manager_id < :id`,
  [110],  // bind value for :id
  { outFormat: oracledb.OUT_FORMAT_OBJECT }
);

console.log(result.rows);

If you want to use the JSON generation functions in Oracle, such as JSON_VALUE, you have to avoid a double parse - just access the string as JSON.

See this series for more info on building a REST API with Node.js and Oracle Database: https://jsao.io/2018/03/creating-a-rest-api-with-node-js-and-oracle-database/

Upvotes: 5

Related Questions