[SalesForce] jsforce Bulk API query is not returning records in proper json format

I am working on a nodejs app that uses jsforce library to query data using Bulk API and the format of data returned doesn't seem to be proper json formatted.

Here is a simple reproducible example:

// JSForce
const jsforce = require('jsforce');

let conn = new jsforce.Connection({
  accessToken:
    '<paste access token here>',
  instanceUrl: 'https://abc.my.salesforce.com'
});

conn.bulk.pollInterval = 1000;
conn.bulk.pollTimeout = Number.MAX_VALUE;
let results = [];

(async () => {
  new Promise((resolve, reject) => {
    conn.bulk
      .query("SELECT Id, FirstName, Profile.Name, IsActive FROM User WHERE Profile.Name = 'System Administrator' LIMIT 1")
      .on('record', function (rec) {
        results.push(rec);
      })
      .on('end', function (rec) {
        console.log('reached end of stream');
        resolve(results);
      });
  }).then(() => {
    console.log(results);
  });

  return conn.query("SELECT Id, FirstName, Profile.Name, IsActive FROM User WHERE Profile.Name = 'System Administrator' LIMIT 1", function (err, result) {
    if (err) {
      console.error(err);
    }
    console.log(result.records);
  });
})();

Now run this code as node test.js and it prints below output:

[
  {
    attributes: {
      type: 'User',
      url: '/services/data/v42.0/sobjects/User/005abc'
    },
    Id: '005abc',
    FirstName: 'John',
    Profile: { attributes: [Object], Name: 'System Administrator' },
    IsActive: false
  }
]
reached end of stream
[
  {
    Id: '005abc',
    FirstName: 'John',
    'Profile.Name': 'System Administrator',
    IsActive: 'false'
  }
]

If we notice the output from Bulk API, it is not same as from the result returned by REST API. Notice how Profile.Name is printed.

To access, profile name, right now I am doing like this : record['Profile.Name'] instead of record.Profile.Name

Is this expected behavior of Bulk API or something internal to jsforce? Any js hacks that I can apply here to get the data in proper json format?

Best Answer

I was able to get proper json formatted records from Bulk API by formatting raw records that jsforce is emitting. I used csvtojson library to listen to the stream that jsforce is returning and parsing each row as json.

Once I set flatKeys and checkType properties to false and true respectively, I am getting data in the format I need and easy to work with. Hope others find this useful.

// JSForce
const jsforce = require("jsforce");
const csv = require("csvtojson");

let conn = new jsforce.Connection({
  accessToken:
    "<paste access token here>",
  instanceUrl: "https://abc.my.salesforce.com",
});

conn.bulk.pollInterval = 1000;
conn.bulk.pollTimeout = Number.MAX_VALUE;
let records = [];

(async () => {
  // We still need recordStream to listen for errors. We'll access the stream
  // directly though, bypassing jsforce's RecordStream.Parsable
  const recordStream = conn.bulk.query(
    "SELECT Id, FirstName, Profile.Name, IsActive FROM User WHERE Profile.Name = 'System Administrator' LIMIT 2"
  );
  const readStream = recordStream.stream();
  const csvToJsonParser = csv({flatKeys: false, checkType: true});
  readStream.pipe(csvToJsonParser);

  csvToJsonParser.on("data", (data) => {
    records.push(JSON.parse(data.toString('utf8')));
  }); 

  new Promise((resolve, reject) => {
    recordStream.on("error", (error) => {
      console.error(error);
      reject(new Error(`Couldn't download results from Salesforce Bulk API`));
    });

    csvToJsonParser.on("error", (error) => {
      console.error(error);
      reject(new Error(`Couldn't parse results from Salesforce Bulk API`));
    });

    csvToJsonParser.on("done", async () => {
      resolve(records);
    });
  }).then((records) => { 
    console.log(records);
  });
})();

This prints the below output:

[
  {
    Id: '005abc',
    FirstName: 'John',
    Profile: { Name: 'System Administrator' },
    IsActive: false
  },
  {
    Id: '005def',
    FirstName: 'Robert',
    Profile: { Name: 'System Administrator' },
    IsActive: false
  }
]
Related Topic