BigQuery support for column containing a repeated record / list

Is it possible when using a BigQuery datasource, to access items in a column that are a list or what BigQuery calls a repeated record?

In one table I have multiple users, and each user has multiple skills with a score. Is there a way to reference that column of skills and scores without creating a seperate table referencing them?

0 2 155
  • UX
2 REPLIES 2

If your values are stored as a delimited list then you should be able to define the column as EnumList, specify the delimiter (Comma is the default) and the column will treat the Skills and Score combo as items from a list.

NOTE: Iโ€™m not sure if this is still the case but at one point AppSheet required the delimiter in the data to be surrounded by spaces - i.e. space in front of the delimiter and a space behind the delimiter - in order to properly recognize the list. If your values are not already stored that way, you may need to reformat the text string in the datasource to meet that requirement. I would try without any changes first to see if it has changed.

Thanks for your reply John. That made me wonder how it was exactly represented, and having a look I discovered the column data is actually in JSON format, and with some interesting characters which must appear during the conversion when adding BigQuery as a datasource. Here is a snippet:

[ { "v": { "f": [ { "v": "3" }, { "v": "2" }, { "v": null }, { "v": "Route 53" }, { "v": "212" } ] } }, { "v": { "f": [ { "v": "11" }, { "v": "1" }, { "v": null }, { "v": "SQLite" }, { "v": "26" } ] } }, { "v": { "f": [ { "v": "5" }, { "v": "1" }, { "v": null } ]

In BigQuery those columns would be skills.subcategory_id = 3, skills.score = 2, skills.category_desc = null, skills.name = โ€œRoute53โ€, etc

Top Labels in this Space