3

Using oracledb node.js driver with outFormat:oracledb.OBJECT option returns json, but coloumn names are formated uppercase (property names follow Oracle's standard name-casing rules), like this: {"ID":"1"} Is it possible to make them in lowercase, like this: {"Id":"1"}?

JSON_OBJECT introduced In Oracle Database 12.2 is not available for me.

MT0
  • 143,790
  • 11
  • 59
  • 117
Mitrich
  • 35
  • 1
  • 4

1 Answers1

7

Just use column aliases:

const oracledb = require('oracledb');
const config = require('./dbConfig.js');

(async function() {
  let conn;
  let result;

  try {
    conn = await oracledb.getConnection(config);

    result = await conn.execute(
     `select first_name || ' ' || last_name name,
        email
      from employees
      where rownum = 1`,
      [], // no binds
      {
        outFormat: oracledb.OBJECT
      }
    );

    // This is the problem, uppercase column names, no?
    console.log(result.rows); // [ { NAME: 'Steven King', EMAIL: 'SKING' } ]

    result = await conn.execute(
     `select first_name || ' ' || last_name "name",
        email "email"
      from employees
      where rownum = 1`,
      [], // no binds
      {
        outFormat: oracledb.OBJECT
      }
    );

    // Here's the result with case sensitve aliases
    console.log(result.rows); // [ { name: 'Steven King', email: 'SKING' } ]
  } catch (err) {
    // Will throw, but only after finally runs
    throw err; 
  } finally {
    if (conn) {
      try {
        await conn.close();
      } catch (err) {
        console.log('error closing conn', err);
      }
    }
  }
}());

Or "process" the results after. You might find this relevant: https://jsao.io/2015/07/relational-to-json-with-node-js/

Dan McGhan
  • 4,479
  • 1
  • 11
  • 15
  • Thanks, but when using connection.execute("SELECT realcolumnname "id" FROM sometable",{}...) receives status 500. – Mitrich Nov 01 '17 at 14:12
  • Do you have a table named sometable? That was just an example, you'll need to adapt it for your environment. The key is to use column aliases that are wrapped in double quotes. – Dan McGhan Nov 01 '17 at 14:52
  • Sure, I've got that, but as writen in 9.1.4, each row is a JavaScript object that specifies column names and their respective values. Note the property names follow Oracle's standard name-casing rules. They will commonly be uppercase, since most applications create tables using unquoted, case-insensitive names. That is exatly what happens and my aim is to overcome this. – Mitrich Nov 01 '17 at 17:50
  • I've updated my answer with an example. Is this not what you're trying to do? – Dan McGhan Nov 01 '17 at 21:21
  • Thank you, your updated is just what I was trying to do. I just made a mess of Select with wrong quotes. – Mitrich Nov 02 '17 at 07:31
  • I was trying oracledb.OBJECT but is deprecated, now is preferred oracledb.OUT_FORMAT_OBJECT – Jonathan Hernández Aug 03 '22 at 19:25