Convert Big object to a primitive data type?

Question

I am new to using BigQuery and NodeJS. I installed the client side library with this command npm install --save @google-cloud/bigquery . Then I set up my credentials and API keys. Then I made an app.js with the following content:

// Import the Google Cloud client library using default credentials
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
async function query() {
  // Queries the U.S. given names dataset for the state of Texas.

  const query = `SELECT AVG(vs)
FROM \`vertical-idea-303617.somedb.testdata\`
WHERE d >= '2021-01-11 18:14:00' AND d < '2021-01-11 21:14:00'
GROUP BY bId, cId, EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d), EXTRACT(DAY FROM d), EXTRACT(HOUR FROM d), EXTRACT(MINUTE FROM d)`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  rows.forEach(row => {
    console.log(row);
  });
}
query();

When I run node app.js, I get results such as:

Job ae0c6e3d-a41f-4ffe-b51e-087ab25cda2d started.
Rows:
{ f0_:
   Big {
     s: 1,
     e: 2,
     c: [ 5, 2, 4, 8 ],
     constructor:
      { [Function: Big]
        DP: 20,
        RM: 1,
        NE: -7,
        PE: 21,
        strict: false,
        Big: [Circular],
        default: [Circular] } } }
{ f0_:
   Big {
     s: 1,
     e: 2,
     c: [ 1, 1, 3, 5 ],
     constructor:
      { [Function: Big]
        DP: 20,
        RM: 1,
        NE: -7,
        PE: 21,
        strict: false,
        Big: [Circular],
        default: [Circular] } } }
etc...

I don't understand what the Big object is. I was expecting decimal or float values. What is this Big object? How do I cast the Big object as a decimal or float?

I'm still trying to navigate the BigQuery documentation but I'm getting lost. Any guidance/direction/feedback would help. Thanks

--

I found that I could do this parseFloat(row.f0_.toString()) to convert the Big to float. But is this best practice?

Answer 1

You can cast your results to a float64 in the query:

const query = `SELECT CAST(AVG(vs) as FLOAT64)
  FROM \`vertical-idea-303617.somedb.testdata\`
  WHERE d >= '2021-01-11 18:14:00' AND d < '2021-01-11 21:14:00'
  GROUP BY bId, cId, EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d),     
  EXTRACT(DAY FROM d), EXTRACT(HOUR FROM d), EXTRACT(MINUTE FROM d)`;

https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions