dbt exteranl-exteranl-tables for json datatype

Help
,
1

Im creating external_tables.yml and configuring the exteranl table location details with stage name, file format . Im consuimg json with variant datatype. I need to explode the columns from variant column with some expression while defining the external table. How to achieve this. Tried adding columns ,datatypes and expressions but getting error as unsupported key expression.

name: ext_header
        external:
          location: "@teststage.test"  
          file_format: "( type = JSON )"      
          pattern: ".*/file1/.*/.*"
        columns:
          - name: RAW_RECORD
            data_type: VARIANT
            description: "Raw JSON record"
            expression: "value::VARIANT" 
          - name: filename
            data_type: VARCHAR
            expression: "SPLIT(METADATA$FILENAME, '/')[0]::STRING"
2

The external table will have a column named value . I parse out all the fields and cast them to datatypes in staging models like: select value:fieldName::type as field_name ... from {{ source('source', 'external_table') }}

Note: @Mark Estey originally posted this reply in Slack. It might not have transferred perfectly.