Snowflake Semi-structured data support

[danfrankj]

I believe this would be a bigger change than simply adding a new dialect as new parsing is required for json extraction syntax

Test Cases

SELECT foo:bar

SELECT foo:bar:baz::DATE

SELECT foo:bar[0]

SELECT foo:bar.baz

Ref:
https://docs.snowflake.com/en/user-guide/querying-semistructured.html#traversing-semi-structured-data

[tobymao]

this is not too difficult, just need to support single semicolon, if you want to take a stab at this, i can walk you through what needs to be done. can do a vid call or some kind of chat app to sync if you're interested.

i'm not familiar with snowflake, but is x:y just JSON_PATH(x, y) or STRUCT_EXTRACT?, seems more similar to struct_extract...

[tobymao]

@danfrankj i'm reading through snowflake docs and i don't quite understand the difference between : and .

can i just convert : -> .? this is a one line change

diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 117b0c9..7edb4a8 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1103,7 +1103,7 @@ class Parser:
             this = self.expression(exp.Column, this=this)
         this = self._parse_bracket(this)
 
-        while self._match(TokenType.DOT):
+        while self._match_set({TokenType.DOT, TokenType.COLON}):
             field = self._parse_id_var() or self._parse_star()
             if isinstance(this, exp.Column) and not this.table:
                 this = self.expression(exp.Column, this=field, table=this.this)

[NodeJSmith]

@tobymao I know this is closed, but I'm hitting the same issue currently with version 10.5.10. Happy to provide more information and/or to open a new issue for this. Below is a screenshot of the issue - I imagine it's because there are double quotes, since that is what seems to differ between my example and the example provided by @danfrankj

[tobymao]

please open a new issue thanks!

[tobymao]

@NodeJSmith i tried to recreate your error from your screen shot but it seems to work

python -m sqlglot 'select generated_features:"uws.rcu"' --read=snowflake
SELECT
  "generated_features"['uws.rcu']

[tobymao]

don't forget to pass the read dialect in as snowflake

[NodeJSmith]

@tobymao fudge, I totally forgot to do that 🤦‍♀️ I'll try it today, I imagine that will fix it. Sorry for the false alarm