Convert Oracle query output to json (Oracle / NodeJS)

Question

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}
]

Dan McGhan · Accepted Answer · 2020-04-03 13:39:42Z

5

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/

edited Apr 3, 2020 at 13:39

answered Apr 2, 2020 at 19:47

Dan McGhan

4,6691 gold badge14 silver badges15 bronze badges

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

2 Comments

VersifiXion Over a year ago

I followed this series but I have an issue with createPool method from oracledb npm package...

VersifiXion Over a year ago

and even with res.json instead of res.send, the ouput still the same

Abdullah · Accepted Answer · 2022-10-18 10:08:28Z

0

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);
}});

answered Oct 18, 2022 at 10:08

Abdullah

3,0611 gold badge23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Convert Oracle query output to json (Oracle / NodeJS)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related