This article will use a Databricks connection in Omni where you can query your struct types. Note that other data platforms like Google Cloud’s BigQuery also have structs and they will follow a similar syntax, like using dot notation, but it’s always good practice to check your data source’s documentation.
A struct type represents values with the structure described by a sequence of fields. This is typically used when you want strict schema enforcement on the object, where a JSON is more flexible and evolving. You may want to query this column without having to unpack the values inside, that’s possible to do in Omni.
Example showing the schema of the struct type, in this case in a Databricks instance, for a column called “info”:
In Omni on the yaml side in your model this column will appear as just another dimension without any special syntax. Here is what that looks like in the following view file:
The connection type in Omni that has the struct type is Databricks, so when you write SQL by selecting ‘Start from SQL’ you should write queries in the appropriate dialect for your data source, here is what that looks like for my Databricks connection:
So you have the flexibility to pull out whatever values you want from the struct type without having to fully model this out as a table(s) before querying in Omni. Should you want to model it so your business users don’t have to write SQL, you have the option to do so in dbt where you can take advantage of macros to parse out information in a DRY manner or follow a more software development lifecycle approach to your data modeling to get feedback in pull requests from your peers on the shape the model is taking. You have a few options in Omni where you can pull out the relevant information in the struct that your business users might be interested in. The first option is you can always “Save as a query view” in Omni, here is a quick example:
This is what your business users will see when using this query view, note your saved SQL logic is injected as a CTE in Omni:
The other option you have in Omni is to put these fields in the view model:
Here is what your business users would see, the fields now appearing as option to query:
Should you run into any issues adding the fields in the view file you can try adding a DO NOT PARSE comment like the following:
