将Oracle查询输出转换为json(Oracle/NodeJS) [英] Convert Oracle query output to json (Oracle / NodeJS)

问题描述

我正在使用React/Express(NodeJS)/Oracle做一个应用程序，

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

我有一条Express路由，可以从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]]

我想要这个:

[
  {"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}
]

推荐答案

为什么要使用JSON_VALUE?驱动程序返回本地JavaScript对象.您可以将查询编写为:

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.

默认情况下，驱动程序返回一个数组数组(无键).如果需要对象数组，则需要将选项对象传递给 execute ，以更改 outFormat .请参阅文档的这一部分: https://oracle.github.io/node-oracledb/doc/api.html#queryoutputformats

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

如果要在Oracle中使用JSON生成功能(例如JSON_VALUE)，则必须避免双重解析-只需将字符串作为JSON访问即可.

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.

有关使用Node.js和Oracle数据库构建REST API的更多信息，请参阅本系列:

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/

这篇关于将Oracle查询输出转换为json(Oracle/NodeJS)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！