node.jsjsonoracle-databasenode-oracledb

Get Node.js oracledb result as JSON


I am using the oracledb package to retrieve some data and output the data as json and I have successfully done that but I want the values to be in quotes for example this is the output i get.

{
   "postage_charge":0,
   "shipping_charge":52.28,
   "order_processing":9.82,
   "receiving_charge":0,
   "storage_charge":21.36,
   "product_charge":2.65,
   "sup_charge":0,
   "hold_charge":0
}

I want it too look like

{
   "postage_charge":"0",
   "shipping_charge":"52.28",
   "order_processing":"9.82",
   "receiving_charge":"0",
   "storage_charge":"21.36",
   "product_charge":"2.65",
   "sup_charge":"0",
   "hold_charge":"0"
}

here is my code

async function selectInvoiceDetail(req, res, invoice) {

    try {
      connection = await oracledb.getConnection({
        user: "",
        password: "",
        connectString: "192.168.1.5:1521/test"
      });
      
      postageCharge = await connection.execute(`select sum(item_total) as postal_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and charge_type='SHIPPING' and charge_subtype='POSTAGE'`);
      shippingCharge = await connection.execute(`select sum(item_total) as shipping_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='SHIPPING' and charge_subtype='SHIPPING' or  charge_type='SUP' and charge_subtype='DUTAX' or  charge_type='SUP' and charge_subtype='SHIPPING' or  charge_type='SUP' and charge_subtype='VASSHIP')`);
      orderProcessing = await connection.execute(`select sum(item_total) as order_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='ORDERS' and charge_subtype='BASE' or  charge_type='ORDERS' and charge_subtype='INTNAT' or  charge_type='ORDERS' and charge_subtype='LTLCP' or  charge_type='ORDERS' and charge_subtype='PACKAGING' or  charge_type='ORDERS' and charge_subtype='PIECE' or  charge_type='ORDERS' and charge_subtype='PIECEC' or  charge_type='ORDERS' and charge_subtype='SERIAL' or  charge_type='SUP' and charge_subtype='ORDERS')`);
      receivingCharge = await connection.execute(`select sum(item_total) as receiving_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='RECEIPT' and charge_subtype='PIECE' or  charge_type='RECEIPT' and charge_subtype='ITEM' or  charge_type='RECEIPT' and charge_subtype='PIECEC' or  charge_type='SUP' and charge_subtype='RECEIVE' or  charge_type='RECEIPT' and charge_subtype='LEVEL')`);
      storageCharge = await connection.execute(`select sum(item_total) as storage_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='STORAGE' and charge_subtype='FLAT' or  charge_type='STORAGE' and charge_subtype='LOCATION' or  charge_type='STORAGE' and charge_subtype='VOLUME')`);
      productCharge = await connection.execute(`select sum(item_total) as product_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and charge_type='PRODUCT' and charge_subtype='SETUP'`);
      supCharge = await connection.execute(`select sum(item_total) as sup_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and (charge_type='SUP' and charge_subtype='RECEIPT' or  charge_type='SUP' and charge_subtype='CRETURN' or  charge_type='SUP' and charge_subtype='LABEL' or  charge_type='SUP' and charge_subtype='RETURN' or  charge_type='SUP' and charge_subtype='VAS')`);
      holdCharge = await connection.execute(`select sum(item_total) as hold_charge from INVOICE_ITEMS where INVOICE_ID='${invoice}' and charge_type='OHOLD' and charge_subtype='PERCENT'`);
      
    } catch (err) {
      return res.send(err.message);
    } finally {
      if (connection) {
        try {
          await connection.close();
        } catch (err) {
          console.error(err.message);
        }
      }
        res.setHeader('Content-Type', 'application/json');

        var result = {
        "postage_charge": postageCharge.rows[0].POSTAL_CHARGE ?? 0,
        "shipping_charge": shippingCharge.rows[0].SHIPPING_CHARGE ?? 0,
        "order_processing": orderProcessing.rows[0].ORDER_CHARGE ?? 0,
        "receiving_charge": receivingCharge.rows[0].RECEIVING_CHARGE ?? 0,
        "storage_charge": storageCharge.rows[0].STORAGE_CHARGE ?? 0,
        "product_charge": productCharge.rows[0].PRODUCT_CHARGE ?? 0,
        "sup_charge": supCharge.rows[0].SUP_CHARGE ?? 0,
        "hold_charge": holdCharge.rows[0].HOLD_CHARGE ?? 0
        };
        
        return res.send(result);
    }
  }

any help is appreciated

thank you.


Solution

  • Just convert number (coming from DB) to string .toString(). In you results :(postageCharge.rows[0].POSTAL_CHARGE ?? 0).toString() and the rest of them which type is number.

    <!DOCTYPE html>
    <html>
    <body>
    
    <h2>convert numbers coming from server to string to get quotes in JSON - toString() Method</h2>
    <p>toString() returns the content of a string:</p>
    <p> your example woudl be : (postageCharge.rows[0].POSTAL_CHARGE ?? 0).toString(),</p>
    <p id="demo"></p>
    
    <script>
    //your example woudl be 
    // (postageCharge.rows[0].POSTAL_CHARGE ?? 0).toString(),
    let fromServer  = 123;
    let result = fromServer.toString();
    document.getElementById("demo").innerHTML = result; 
    </script>
    
    </body>
    </html>

    number toString Mozilla documentation