Can't apply function eval for each row

[rafael-bg]

Hi, I have a need to use the eval() function to calculate string expressions for each row of the table. When I use hardcoded string expressions, it works. However, dynamically for each row, it does not work:

Even using a function exported from the JS file:

[ms4446 · Accepted solution]

BigQuery does not directly support the eval() function as it is part of JavaScript, not SQL or BigQuery's functions. However,  here are some strategies to achieve your goal safely and effectively:

1. JavaScript User-Defined Functions (UDFs) BigQuery allows for the creation of UDFs using JavaScript, which can provide a workaround for evaluating expressions:

   CREATE TEMPORARY FUNCTION calculateExpression(expression STRING) 
   RETURNS FLOAT64 
   LANGUAGE js AS """ 
       // **IMPORTANT: Implement rigorous input validation and safe parsing here**  
       return /* Your safe evaluation logic */; 
   """; 
   
   SELECT expression, calculateExpression(expression) AS calculated_result 
   FROM raw_data; 
   

   • Security: Prioritize input validation to mitigate risks.

2. CASE Expressions and SQL Logic For simpler or pattern-based expressions, SQL's CASE statements or other conditional logic can be used:

   SELECT expression, 
          CASE WHEN expression LIKE '%/%' THEN /* Division logic */ 
               WHEN expression LIKE '%+%' THEN /* Addition logic */ 
               -- Add more cases as needed 
          END AS calculated_result 
   FROM raw_data; 
   

3. Pre-Calculation Evaluate expressions before loading the data into BigQuery using a scripting or programming language.

4. Client-Side Evaluation After retrieving data from BigQuery, use a client-side environment to evaluate expressions.

Choosing the Best Approach

The optimal strategy depends on these factors:

• Complexity of Expressions: Simple patterns might be efficiently handled with SQL logic, while more complex expressions could necessitate JavaScript UDFs.

• Security: Always prioritize security by sanitizing and validating inputs, especially when evaluating expressions dynamically.

• Performance: Test and optimize your solution, as JavaScript UDFs might impact query performance for large datasets.

[rafael-bg]

Using 1. JavaScript User-Defined Functions (UDFs) was the solution.