Snowflake JSON suport

[simon . schenk]

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