Parsing Json schema using DBT & Snowflake

Help
1

The problem I’m having

Unable to parse the Json Schema

The context of why I’m trying to do this

Getting source Data in Json format and being stored in Snowflake using Variant Datatype.

What I’ve already tried

This code working fine in Snowflake but thorwing compilation error in dbt
WITH src_custom_fields_new AS (
SELECT
*
FROM
src_custom_fields
)
select
i.value:id ::string,
i.value:name ::varchar,
i.value:datatype::varchar,
i.value:source::varchar as datafeedname
from src_custom_fields_new,
lateral flatten(input=>src_custom_fields_new.value) i

Some example code or error messages

Put code inside backticks
  to preserve indentation
    which is especially important 
      for Python and YAML!
2

You have to quote the JSON fields
e.g.

select
  i.value:"id"::string
3

Thanks @tdkaraffa for getting back to me. As suggested I changed the code as below:
WITH src_custom_fields_new AS (
SELECT
*
FROM
{{ ref(‘src_custom_fields’) }}
)
select
i.value:“id” ::varchar,
i.value:“name” ::varchar,
i.value:“datatype”::varchar,
i.value:“source”::varchar as datafeedname
from src_custom_fields_new,
lateral flatten(input=>src_custom_fields_new.value) i

But still I am getting following error:

" Completed with 1 error and 0 warnings:
Database Error in model cust_fields (models\dim\cust_fields.sql)
002022 (42601): SQL compilation error:
Missing column specification"

4

I’d also note that the CTE doesn’t do anything, you can (and should) just select the value column directly from it in the lateral flatten
Which brings me to this point: is the column you want to flatten named value? It’s confusing because you also have the keyword .value to select from the JSON object, but those are two different values.

5

You might also have to name all of the fields you’re selecting from the JSON object, looks like the “id”, “name”, and “datatype” selections don’t have names.