Snowflake nested fields are incorrectly translated using JSON_EXTRACT

[Leonti]

Before you file an issue

• Make sure you specify the "read" dialect eg. parse_one(sql, read="spark")
• Make sure you specify the "write" dialect eg. ast.sql(dialect="duckdb")
• Check if the issue still exists on main

Fully reproducible code snippet

print(sqlglot.transpile("select payload:testField from snowflake_table;", read="snowflake", write="trino", pretty=True)[0])

Output:

SELECT
  JSON_EXTRACT(payload, '$.testField')
FROM snowflake_table

Expected output:

SELECT
  payload.testField
FROM snowflake_table

payload:testField is how nested fields of VARIANT type are accessed in Snowflake, they should be translated into payload.testField for Trino, since they would be or row type in Trino and not JSON.
Official Documentation
Querying VARIANT nested fields in Snowflake
Trino nested field query example

[VaggelisD]

Hey @Leonti, thanks for reporting this. Right now, SQLGlot generates mostly JSONPath functions when parsing semi-structured accesses (e.g. GET_PATH for VARIANT in Snowflake) instead of preserving the colon/dot notation.

However, I think the Snowflake -> Trino transpilation is more nuanced and will probably require type inference to work properly, so the fix will be a best-effort attempt without a schema.

[VaggelisD]

Quick follow up to gain more context, could you provide an example of how your data is structured in Snowflake and in what way would they be transformed to Trino's ROW type instead of JSON?

[Leonti]

Hi @VaggelisD!
Thanks for looking into it so quickly!
I have an Iceberg table in S3 which is integrated with Snowflake.
The table has a couple of columns which are of type STRUCT in Iceberg, which translates into VARIANT when integrated with Snowflake. If I need to get access to nested fields using Snowflake I would do something like:

select payload:nestedField:nestedNestedField from iceberg_table;

because it's a VARIANT type.
Because fields are nested within a a STRUCT I've created a "convenience view" in snowflake that flattens the structure, something like this:

select 
payload:nestedField:nestedNestedField as field,
payload:anotherField as field2,
-- and so on, potentially tens of fields
from iceberg_table;

Since it's an Iceberg table it can also be queried directly from Athena/Trino. In Trino Iceberg/Parquet STRUCT is represented as ROW type, and instead of using : one would use . notation to access nested fields.
Since I already have a bunch of "convenience views" for Snowflake, I was hoping to use sqlglot to convert them to query the Iceberg table using Athena/Trino instead of converting them by hand.
When using Iceberg or Parquet on S3 the STRUCT type gets converted into Snowflake's VARIANT or Trino's ROW directly, so JSON is not involved there at all.

[VaggelisD]

Thanks for the detailed explanation! Did some research over the last days and came to the conclusion that there's ambiguity in transpiling Snowflake's VARIANT as it can store practically any data type. For example, it can store semi-structured objects such as JSON (in which case, the transpilation to Trino's JSON_EXTRACT is correct) or a structured OBJECT in the case of Iceberg's STRUCT, which should be transpiled as you've explained.

Since SQLGlot cannot analyze the data to infer what's stored under VARIANT, I'll attempt to make the transpilation configurable such that you can generate both versions (JSON_EXTRACT and dot notation, depending on the setting) on Snowflake -> Presto/Trino, hopefully that will help.