sir_chocolate_soup
sir_chocolate_soup

Reputation: 449

Bigquery JSON - Nested arrays not allowed error troubleshooting

New to bigquery here. I've been following the documentation on how to create an empty schema and do a bq load job on my json data and working through each error message that I get during the bq load job.

There is one error message that has been giving me some trouble, and the details around the message aren't too helpful beyond the fact that it is a nested array error.

Error while reading data, error message: JSON table
encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[] collection for more details.
Failure details:
- Error while reading data, error message: JSON processing
encountered too many errors, giving up. Rows: 1; errors: 1; max
bad: 0; error percent: 0
- Error while reading data, error message: JSON parsing error in row
starting at position 0: Nested arrays not allowed.

Below is the JSON file that I used to make an empty table based on this schema which was successfully created. By that, I had assumed that this data schema is valid, but when I try the load job with my data, I get the error above.

Can someone point me in the right place to look for why I might be getting this error? Are the arrays in the 'tags' subfield violating this 'nested array' rule? If so, why would the bq mk statement work to create an empty table with the schema? I see examples of valid data schemas for bigquery, but not ones where it isn't allowed which would be helpful in my case.

Any guidance is appreciated. Thanks!

[
   {
       "name":"team_name",
       "type":"STRING",
       "mode":"NULLABLE"
   },
   {
       "name":"team_attributes",
       "type":"RECORD",
       "mode":"NULLABLE",
       "fields": [
         {
           "name":"team_attributes_raw",
           "type":"STRING",
           "mode":"NULLABLE"
         },
         {
           "name":"team_country",
           "type":"STRING",
           "mode":"NULLABLE"
         },
         {
           "name":"team_type",
           "type":"STRING",
           "mode":"NULLABLE"
         },
         {
           "name":"tags",
           "type":"STRING",
           "mode":"REPEATED"
         }
       ]
   }, 
   {
       "name":"players",
       "type":"RECORD",
       "mode":"REPEATED",
       "fields": [
           {
               "name":"player_key",
               "type":"STRING",
               "mode":"NULLABLE"
           },
           {
               "name":"player_attributes",
               "type":"RECORD",
               "mode":"NULLABLE",
               "fields": [
                   {
                       "name":"player_name",
                       "type":"STRING",
                       "mode":"NULLABLE"
                   },
                   {
                       "name":"product_url",
                       "type":"STRING",
                       "mode":"NULLABLE"
                   },
                   {
                       "name":"measurement",
                       "type":"RECORD",
                       "mode":"NULLABLE",
                       "fields": [
                         {
                           "name":"measurement_raw",
                           "type":"STRING",
                           "mode":"NULLABLE"
                         },
                         {
                           "name":"player_weight",
                           "type":"FLOAT",
                           "mode":"NULLABLE"
                         },
                         {
                           "name":"player_unit_of_measurement",
                           "type":"STRING",
                           "mode":"NULLABLE"
                         },
                         {
                           "name":"tags",
                           "type":"STRING",
                           "mode":"REPEATED"
                         }
                       ]
                   }
               ]
           }
       ]
   }
]   

Upvotes: 1

Views: 2137

Answers (1)

sir_chocolate_soup
sir_chocolate_soup

Reputation: 449

It turns out that this schema is totally acceptable (as tested by the 'bq mk' command used to create a table. The error was given by an element in the repeated field (array) which looked like

["1", [] ]

Triple check next time!

Upvotes: 1

Related Questions