I wasn’t aware of this Snowflake functionality. It looks very nice.
First, note that Calcite already has JSON support consistent with the SQL
standard. See https://calcite.apache.org/docs/reference.html#json-functions
<https://calcite.apache.org/docs/reference.html#json-functions>.
If we were to implement it in Calcite, I can see few distinct pieces:
1. Support for the ‘:’ (field reference) operator in the parser. We already
have ‘::’ (Postgres-style cast) in the Babel parser. We’d have to check that
‘:’ does not introduce ambiguities.
2. Add a VARIANT datatype. This is similar to SQL Server’s VARIANT type and
Java’s Object type in that it is a tagged union: it can be many sub-types, and
you can check the type at runtime using operators such as TYPEOF.
3. We already have the ITEM operator (written using '[ ]') that works on arrays
and maps. We should make ITEM also work on VARIANT.
4. Add a function to parse a JSON string to a VARIANT object.
The pieces are fairly independent. You could do 2, 3, 4 without 1, and do 1
without 2, 3, 4.
Julian
> On Nov 11, 2021, at 3:18 AM, simon.sch...@ziverge.com wrote:
>
> 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
>
