CODEWITHSUNDEEP
google-bigqueryuser-defined-functions
Charles Chow
Charles Chow

Reputation: 1067

How to use escape quote in Bigquery query text?

I defined and registered a UDF function in a query, but it looks like the big query doesn't like the escape quote \" in the function definition string, does anyone know how to use escape quote in bigquery?

This is my example:

SELECT
  Social_Connection,
  Device_Type
FROM
  js(
    -- input table
    (
    SELECT
      user_attribute.Name,
      user_attribute.Value
    FROM
      UDF_TESTING.testing_src ),
    -- input vars
    user_attribute.Name,
    user_attribute.Value,
    -- output schema
    "[{name: 'Social_Connection', type: 'string'},
   {name: 'Device_Type', type: 'string'}]",
    -- the function
    "function(row, emit) {
     var social_connection_index = 0;
     var device_type_index = 0;
  for (var i = 0; i < row.user_attribute.length; i++) {
    if (row.user_attribute[i].Name == \"Social_Connection\") {    // <------big query complains about the escape quote
      social_connection_index = i;
    }
    if (row.user_attribute[i].Name == \"Device_Type\") {   // <----- same as here
      device_type_index = i;
    }
  }
  emit( {Social_Connection: row.user_attribute[social_connection_index].Value,
         Device_Type: row.user_attribute[device_type_index].Value} )
}")

Upvotes: 1

Views: 5501

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is dummy example, just to demonstrate how to use escape in both BQ SELECT itself as well as within JS function.
Hope this will help you! 

SELECT Name, HasSingleQuote, HasDoubleQuote
FROM ( JS(
    -- input table
    (SELECT
      Name
    FROM
      (SELECT 'abc' AS Name),
      (SELECT 'a\'bc' AS Name),
      (SELECT 'a\"bc' AS Name),
      (SELECT 'a\"b\'c' AS Name)
      ),
    -- input vars
    Name,
    -- output schema
    "[{name: 'Name', type: 'STRING'},
   {name: 'HasSingleQuote', type: 'BOOLEAN'},
   {name: 'HasDoubleQuote', type: 'BOOLEAN'}]",
    -- the function
    "function(row, emit) {
      var hasSingleQuote = false;
      var hasDoubleQuote = false;
      if (row.Name.indexOf('\'') > -1) hasSingleQuote = true;
      if (row.Name.indexOf('\"') > -1) hasDoubleQuote = true;
      emit( {Name: row.Name,
         HasSingleQuote: hasSingleQuote,
         HasDoubleQuote: hasDoubleQuote
         } )
}"))

Output is: 

Name    HasSingleQuote  HasDoubleQuote   
abc     false           false    
a'bc    true            false    
a"bc    false           true
a"b'c   true            true

Upvotes: 3

Related Questions