Hi,
I'm experimenting with a dialect for Snowflake. In particular I would
like to be able to parse Snowflake JSON expressions, which work on
VARIANT columns as follows:
Give a JSON structure like this:
{
"fullName": "Johnny Appleseed",
"phoneNumber": {
"areaCode": "415",
"subscriberNumber": "5551234",
"extensionNumber": "24"
}
}
And a schema like this:
create table json_demo (v variant);
The following should work:
select
v:phoneNumber.areaCode::string as area_code,
v:phoneNumber.subscriberNumber::string as subscriber_number,
v:phoneNumber.extensionNumber::string as extension_number
from json_demo;
Basically we have a column name, colon, compound identifier, double
colon, type.
(example taken from
https://www.snowflake.com/wp-content/uploads/2017/08/Snowflake-How-to-Analyze-JSON-with-SQL.pdf)
As far as I understand (which is not too much, as I'm a Calcite noob)
this is not easily possible using one of the existing extension points,
as I need a new kind of identifier.
Could you give me a few pointers to the right approach?
Best regards,
Simon
