Why can't I access subcolumns (nested fields) in Alteryx Visual Query Builder like I can i

[charliefulker]

Hi Community,

I'm working with Google BigQuery as my data source and using Alteryx Visual Query Builder to query tables. I’ve noticed that when I connect to the dataset and select a table like monetization_product_event_v2, I can only see top-level fields such as eventUUID, createdAt, context, etc.

However, fields like context.bookingUUID, context.currency, or product.contractNumber (which are nested JSON fields) are not available for selection or expansion in the Visual Query Builder. They appear just as single-level fields and I can't drill into them.

On the other hand, when I use the same table in BigQuery's UI, I can directly reference these nested fields using dot notation (e.g. C.context.bookingUUID) and build complex queries successfully (see example query below).

I need to recreate a similar query in Alteryx and input it into the SQL Editor tab, but it won’t validate because Alteryx doesn’t seem to recognize the nested fields.

Question:

Is there a way to enable or access nested fields in the Visual Query Builder in Alteryx?

If not, is there a workaround or best practice for referencing these nested fields in Alteryx SQL Editor when using BigQuery?

Does Alteryx require flattening of nested structures in BigQuery before they can be queried properly?

Any guidance would be appreciated!

Thanks in advance!

[KGT]

Note: I don't have experience with Bigquery in this instance, but I do have a fair bit of experience with Alteryx and how it works. Adding this, because there's been no response for a couple weeks.

I don't expect the visual query builder to look into the nested fields, as that wouldn't still be classed as Metadata, which is what the Visual Query Builder would work off. Therefore flattening it would be what's required. However, you may be able to replicate this with SQL and the In-Db tools.