Cannot pass input field of repeated record type into Bigquery UDF

1
votes

When I pass an input field of repeated record type into Bigquery UDF, it keeps saying that the input field is not found.

This is my 2 rows of data:

{"name":"cynthia", "Persons":[ { "name":"john","age":1},{"name":"jane","age":2}  ]}
{"name":"jim","Persons":[ { "name":"mary","age":1},{"name":"joe","age":2}  ]}

This is the schema of the data:

[ {"name":"name","type":"string"},

{"name":"Persons","mode":"repeated","type":"RECORD",
    "fields":
    [
        {"name": "name","type": "STRING"},
        {"name": "age","type": "INTEGER"}
    ]
}

]

And this is the query:

SELECT
  name,maxts

FROM
js
(
  //input table
  [dw_test.clokTest_bag],

  //input columns
  name, Persons,

  //output schema
  "[
    {name: 'name', type:'string'},
    {name: 'maxts', type:'string'}
  ]",

  //function
  "function(r, emit)
  {
    emit({name: r.name, maxts: '2'});
  }"
)
LIMIT 10

Error I got when trying to run the query:

Error: 5.3 - 15.6: Undefined input field Persons Job ID: ord2-us-dc:job_IPGQQEOo6NHGUsoVvhqLZ8pVLMQ

Would someone please help?

Thank you.

1
google-bigquery

1 Answers

5
votes

In your list of input columns, list the leaf fields directly:

//input columns
name, Persons.name, Persons.age,

They'll still appear in their proper structure when you get the records in your UDF.