Nested/Repeated Fields from BigQuery in the GUI Editor?

cbeauch · September 17, 2024, 5:05pm

Hey, question around nested fields from BigQuery. Is it possible to use these? I've been playing around and haven't had much luck.

In SQL, I would do something like

SELECT *
FROM `asana_task_tracking`, UNNEST(labels) as label

But I'm curious if non SQL users could use these values and filter by them. Am I missing something or is the functionality not there?

If it's not possible, what would be the best way to filter by these values? Turn the field into a simple json {label1, label2, label3} and just use "contains"?

Screenshot 2024-09-17 at 11.03.19 AM

TonyC · September 18, 2024, 8:24pm

Yeah, or a text field type

TonyC · September 18, 2024, 8:25pm

github.com/metabase/metabase

Support Arrays in DBs (e.g. support things like filtering against their values)

opened 12:53PM - 19 Jul 16 UTC

IdanWo

Querying/MBQL Administration/Metadata & Sync Type:New Feature Querying/GUI

My database is Mongodb. I have fields of type array, meaning some fields in …the document contain array of items. Some of the items in the array are key-value, and others are objects. Although mongodb is officially supported by metabase, it seems that it's not supported to make any filtering or counting upon results consist of arrays. Does saving the items in the array as json would do the trick? I believe it's really a major issue that at least must be documented clearly to the consumers. What do you think? :arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment

cbeauch · September 20, 2024, 9:02pm

What about this?

github.com/metabase/metabase

Support nested records in BigQuery

opened 07:44PM - 24 May 16 UTC

closed 10:05PM - 23 Aug 24 UTC

gad2103

Priority:P2 Type:New Feature Database/BigQuery .Backend Querying/GUI .Team/Querying

@luizarakaki edit (Aug 2024): ```[tasklist] ### Tasklist - [ ] https://github.c…om/metabase/metabase/issues/46977 - [ ] https://github.com/metabase/metabase/issues/46978 ``` @luizarakaki edit (Dec 2022): BigQuery differs from other databases as nested fields are part of the table definition. We don't need to parse JSON fields to get the schema, it is defined in the table schema. https://cloud.google.com/bigquery/docs/information-schema-tables So, instead of using the standard SQL JDBC driver implementation [`describe-nested-field-columns`](https://github.com/metabase/metabase/blob/d8486fccb407b4ef936e04198b97a077ba896990/src/metabase/driver/sql_jdbc/sync/describe_table.clj#L374), we can get the schema from the table metadata. The syntax to query nested fields is also different: it uses the dot notation: ``` SELECT nested.field_1, nested.field_2 FROM table ``` https://scosco.github.io/learn-bigquery/foundation/structs.html **This issue DOESN'T entail supporting arrays (aka repeated). It also doesn't entail working with the new [BigQuery JSON support](https://cloud.google.com/bigquery/docs/release-notes#January_06_2022), this totally unstructured JSON support is new and not frequent (yet).** :arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment

I've been trying to find docs but no luck so far

cbeauch · September 20, 2024, 9:44pm

Based on docs/developers-guide/driver-changelog.md having the change in 0.51, I am going to assume this functionality will be available in Metabase 0.51