Node JS - Big Query update table with a request object

Question

Based on below request object I need to update the table

var reqBody = {
   "Name":"testing11",
   "columns":[
      {
         "fieldExistsIn":"BOTH",
         "columnWidth":5,
         "hide":false
      },
      {
         "fieldExistsIn":"BOTH",
         "columnWidth":10,
         "hide":false
      }
   ],
   "Range":{
      "startDate":"20-Oct-2022",
      "endDate":"26-Oct-2022"
   }
}


UPDATE table_name
SET requestData = reqBody
WHERE requestData.Name = reqBody.oldName;

I am doing the insertion using the below query

await bigquery
    .dataset(datasetId)
    .table(tableId)
    .insert(reqBody);

For table schema you can refer the below question

Node JS - Big Query insert to a request object fully into a record data type

user_15747106 · Answer

As per the table schema and sample data you have provided, I tried to replicate it on my end.

Table schema:

As per your requirement, I have modified the code by referring to the queryParamsStructs and query sample codes from Google BigQuery Node.js Client API. To update multiple columns in a table using a JSON object through the BigQuery Client API, we have to write an UPDATE query and pass it in the code. The JSON object should be passed in params and you should have to access that JSON object in the UPDATE query as like below:

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 = `UPDATE
     \`ProjectID.DatasetID.TableID\`
       SET reqData.columns = ARRAY(
       SELECT AS STRUCT * FROM UNNEST(@reqData.columns)
       ),
       reqData.Range.startDate = CAST(@reqData.Range.startDate AS DATE),
       reqData.Range.endDate = CAST(@reqData.Range.endDate AS DATE)
       WHERE reqData.Name = @reqData.Name`;
   // 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',
     params: {
           "reqData": {
             "Name": "testing",
             "columns": [
               {
                 "fieldExistsIn": "One",
                 "columnWidth": 1,
                 "hide": true
               },
               {
                 "fieldExistsIn": "four",
                 "columnWidth": 4,
                 "hide": true
               }
             ],
             "Range":{
               "startDate": "2021-1-11",
               "endDate": "2022-2-22"
            }
           }
       },
   };
   // 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));
 }
 // [END bigquery_query]
 query();

Initial Data:

Updated Result:

Note: If you have inserted the rows to the table recently by using streaming insert, the rows cannot be modified with UPDATE, DELETE or MERGE within the last 30 minutes. Refer to this limitations doc for more information. If you tried to update a table within the last 30 mins, you will get the below error:

UnhandledPromiseRejectionWarning: Error: UPDATE or DELETE statement over table projectID.datasetID.tableID would affect rows in the streaming buffer, which is not supported

Node JS - Big Query update table with a request object

1 Answers