Repository: AfterShip/clickhouse-sql-parser
Pull request #117 (2025-01-07 13:20:58)

Add support of SKIP options for JSON type
```
<column_name> JSON(max_dynamic_paths=N, max_dynamic_types=M, some.path TypeName, SKIP path.to.skip, SKIP REGEXP 'paths_regexp')
```

Only support `SKIP PATH` and `SKIP REGEXP` in this PR, other options will be supported in further PR.

coveralls: ## Pull Request Test Coverage Report for [Build 12652509752](https://coveralls.io/builds/71616691)


### Details

* **101** of **135**   **(74.81%)**  changed or added relevant lines in **4** files are covered.
* No unchanged relevant lines lost coverage.
* Overall coverage increased (+**0.2%**) to **60.52%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [parser/parser_table.go](https://coveralls.io/builds/71616691/source?filename=parser%2Fparser_table.go#L516) | 0 | 1 | 0.0%
| [parser/ast_visitor.go](https://coveralls.io/builds/71616691/source?filename=parser%2Fast_visitor.go#L699) | 3 | 5 | 60.0%
| [parser/ast.go](https://coveralls.io/builds/71616691/source?filename=parser%2Fast.go#L3408) | 46 | 61 | 75.41%
| [parser/parser_column.go](https://coveralls.io/builds/71616691/source?filename=parser%2Fparser_column.go#L931) | 52 | 68 | 76.47%
<!-- | **Total:** | **101** | **135** | **74.81%** | -->


|  Totals | [![Coverage Status](https://coveralls.io/builds/71616691/badge)](https://coveralls.io/builds/71616691) |
| :-- | --: |
| Change from base [Build 12540630182](https://coveralls.io/builds/71524309): |  0.2% |
| Covered Lines: | 6489 |
| Relevant Lines: | 10722 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/parser/ast.go b/parser/ast.go
index 5c5b920..548f1b3 100644
--- a/parser/ast.go
+++ b/parser/ast.go
@@ -1371,7 +1371,7 @@ func (a *TableIndex) String() string {
 	builder.WriteString("INDEX")
 	builder.WriteByte(' ')
 	builder.WriteString(a.Name.String())
-	// a.ColumnDef = *Ident --- e.g. INDEX idx column TYPE ...
+	// a.ColumnDef = *Name --- e.g. INDEX idx column TYPE ...
 	// a.ColumnDef = *ParamExprList --- e.g. INDEX idx(column) TYPE ...
 	if _, ok := a.ColumnExpr.Expr.(*Ident); ok {
 		builder.WriteByte(' ')
@@ -3366,6 +3366,104 @@ func (s *ScalarType) Type() string {
 	return s.Name.Name
 }
 
+type JSONPath struct {
+	Idents []*Ident
+}
+
+func (j *JSONPath) String() string {
+	var builder strings.Builder
+	for i, ident := range j.Idents {
+		if i > 0 {
+			builder.WriteString(".")
+		}
+		builder.WriteString(ident.String())
+	}
+	return builder.String()
+}
+
+type JSONOption struct {
+	SkipPath  *JSONPath
+	SkipRegex *StringLiteral
+}
+
+func (j *JSONOption) String() string {
+	var builder strings.Builder
+	if j.SkipPath != nil {
+		builder.WriteString("SKIP ")
+		builder.WriteString(j.SkipPath.String())
+	}
+	if j.SkipRegex != nil {
+		builder.WriteString(" SKIP REGEXP ")
+		builder.WriteString(j.SkipRegex.String())
+	}
+	return builder.String()
+}
+
+type JSONOptions struct {
+	LParen Pos
+	RParen Pos
+	Items  []*JSONOption
+}
+
+func (j *JSONOptions) Pos() Pos {
+	return j.LParen
+}
+
+func (j *JSONOptions) End() Pos {
+	return j.RParen
+}
+
+func (j *JSONOptions) String() string {
+	var builder strings.Builder
+	builder.WriteByte('(')
+	for i, item := range j.Items {
+		if i > 0 {
+			builder.WriteString(", ")
+		}
+		builder.WriteString(item.String())
+	}
+	builder.WriteByte(')')
+	return builder.String()
+}
+
+type JSONType struct {
+	Name    *Ident
+	Options *JSONOptions
+}
+
+func (j *JSONType) Pos() Pos {
+	return j.Name.NamePos
+}
+
+func (j *JSONType) End() Pos {
+	if j.Options != nil {
+		return j.Options.RParen
+	}
+	return j.Name.NameEnd
+}
+
+func (j *JSONType) String() string {
+	var builder strings.Builder
+	builder.WriteString(j.Name.String())
+	if j.Options != nil {
+		builder.WriteString(j.Options.String())
+	}
+	return builder.String()
+}
+
+func (j *JSONType) Type() string {
+	return j.Name.Name
+}
+
+func (j *JSONType) Accept(visitor ASTVisitor) error {
+	visitor.enter(j)
+	defer visitor.leave(j)
+	if err := j.Name.Accept(visitor); err != nil {
+		return err
+	}
+	return visitor.VisitJSONType(j)
+}
+
 type PropertyType struct {
 	Name *Ident
 }
diff --git a/parser/ast_visitor.go b/parser/ast_visitor.go
index 5595204..6bd10e7 100644
--- a/parser/ast_visitor.go
+++ b/parser/ast_visitor.go
@@ -75,6 +75,7 @@ type ASTVisitor interface {
 	VisitColumnDef(expr *ColumnDef) error
 	VisitColumnExpr(expr *ColumnExpr) error
 	VisitScalarType(expr *ScalarType) error
+	VisitJSONType(expr *JSONType) error
 	VisitPropertyType(expr *PropertyType) error
 	VisitTypeWithParams(expr *TypeWithParams) error
 	VisitComplexType(expr *ComplexType) error
@@ -693,6 +694,13 @@ func (v *DefaultASTVisitor) VisitScalarType(expr *ScalarType) error {
 	return nil
 }
 
+func (v *DefaultASTVisitor) VisitJSONType(expr *JSONType) error {
+	if v.Visit != nil {
+		return v.Visit(expr)
+	}
+	return nil
+}
+
 func (v *DefaultASTVisitor) VisitPropertyType(expr *PropertyType) error {
 	if v.Visit != nil {
 		return v.Visit(expr)
diff --git a/parser/keyword.go b/parser/keyword.go
index beac806..3486735 100644
--- a/parser/keyword.go
+++ b/parser/keyword.go
@@ -105,6 +105,7 @@ const (
 	KeywordIs           = "IS"
 	KeywordIs_object_id = "IS_OBJECT_ID"
 	KeywordJoin         = "JOIN"
+	KeywordJSON         = "JSON"
 	KeywordKey          = "KEY"
 	KeywordKill         = "KILL"
 	KeywordLast         = "LAST"
@@ -158,6 +159,7 @@ const (
 	KeywordQuota        = "QUOTA"
 	KeywordRange        = "RANGE"
 	KeywordRefresh      = "REFRESH"
+	KeywordRegexp       = "REGEXP"
 	KeywordReload       = "RELOAD"
 	KeywordRemove       = "REMOVE"
 	KeywordRename       = "RENAME"
@@ -180,6 +182,7 @@ const (
 	KeywordSettings     = "SETTINGS"
 	KeywordShow         = "SHOW"
 	KeywordShutdown     = "SHUTDOWN"
+	KeywordSkip         = "SKIP"
 	KeywordSource       = "SOURCE"
 	KeywordStart        = "START"
 	KeywordStop         = "STOP"
@@ -329,6 +332,7 @@ var keywords = NewSet(
 	KeywordIs,
 	KeywordIs_object_id,
 	KeywordJoin,
+	KeywordJSON,
 	KeywordKey,
 	KeywordKill,
 	KeywordLast,
@@ -381,6 +385,8 @@ var keywords = NewSet(
 	KeywordQueues,
 	KeywordQuota,
 	KeywordRange,
+	KeywordRefresh,
+	KeywordRegexp,
 	KeywordReload,
 	KeywordRemove,
 	KeywordRename,
@@ -403,6 +409,7 @@ var keywords = NewSet(
 	KeywordSettings,
 	KeywordShow,
 	KeywordShutdown,
+	KeywordSkip,
 	KeywordSource,
 	KeywordStart,
 	KeywordStop,
diff --git a/parser/parser_column.go b/parser/parser_column.go
index 03da914..6aafb8d 100644
--- a/parser/parser_column.go
+++ b/parser/parser_column.go
@@ -810,10 +810,14 @@ func (p *Parser) parseColumnType(_ Pos) (ColumnType, error) { // nolint:funlen
 	if p.tryConsumeTokenKind(TokenKindLParen) != nil {
 		switch {
 		case p.matchTokenKind(TokenKindIdent):
-			if ident.Name == "Nested" {
+			switch ident.Name {
+			case "Nested":
 				return p.parseNestedType(ident, p.Pos())
+			case "JSON":
+				return p.parseJSONType(ident, p.Pos())
+			default:
+				return p.parseComplexType(ident, p.Pos())
 			}
-			return p.parseComplexType(ident, p.Pos())
 		case p.matchTokenKind(TokenKindString):
 			if peekToken, err := p.lexer.peekToken(); err == nil && peekToken.Kind == TokenKindSingleEQ {
 				// enum values
@@ -920,6 +924,81 @@ func (p *Parser) parseColumnTypeWithParams(name *Ident, pos Pos) (*TypeWithParam
 	}, nil
 }
 
+func (p *Parser) parseJSONPath() (*JSONPath, error) {
+	idents := make([]*Ident, 0)
+	ident, err := p.parseIdent()
+	if err != nil {
+		return nil, err
+	}
+	idents = append(idents, ident)
+
+	for !p.lexer.isEOF() && p.tryConsumeTokenKind(TokenKindDot) != nil {
+		ident, err := p.parseIdent()
+		if err != nil {
+			return nil, err
+		}
+		idents = append(idents, ident)
+	}
+	return &JSONPath{
+		Idents: idents,
+	}, nil
+}
+
+func (p *Parser) parseJSONOption() (*JSONOption, error) {
+	switch {
+	case p.tryConsumeKeyword(KeywordSkip) != nil:
+		if p.tryConsumeKeyword(KeywordRegexp) != nil {
+			regex, err := p.parseString(p.Pos())
+			if err != nil {
+				return nil, err
+			}
+			return &JSONOption{
+				SkipRegex: regex,
+			}, nil
+		}
+		jsonPath, err := p.parseJSONPath()
+		if err != nil {
+			return nil, err
+		}
+		return &JSONOption{
+			SkipPath: jsonPath,
+		}, nil
+	default:
+		return nil, fmt.Errorf("unexpected token kind: %s", p.lastTokenKind())
+	}
+}
+
+func (p *Parser) parseJSONType(name *Ident, pos Pos) (*JSONType, error) {
+	if p.matchTokenKind(TokenKindLParen) {
+		return &JSONType{Name: name}, nil
+	}
+
+	options := make([]*JSONOption, 0)
+	for !p.lexer.isEOF() && !p.matchTokenKind(TokenKindRParen) {
+		option, err := p.parseJSONOption()
+		if err != nil {
+			return nil, err
+		}
+		options = append(options, option)
+		if p.tryConsumeTokenKind(",") == nil {
+			break
+		}
+	}
+
+	rparenPos := p.Pos()
+	if _, err := p.consumeTokenKind(TokenKindRParen); err != nil {
+		return nil, err
+	}
+	return &JSONType{
+		Name: name,
+		Options: &JSONOptions{
+			LParen: pos,
+			RParen: rparenPos,
+			Items:  options,
+		},
+	}, nil
+}
+
 func (p *Parser) parseNestedType(name *Ident, pos Pos) (*NestedType, error) {
 	columns, err := p.parseTableColumns()
 	if err != nil {
diff --git a/parser/parser_table.go b/parser/parser_table.go
index e21fb5c..faf6649 100644
--- a/parser/parser_table.go
+++ b/parser/parser_table.go
@@ -513,7 +513,7 @@ func (p *Parser) parseTableArgExpr(pos Pos) (Expr, error) {
 	case p.matchTokenKind(TokenKindInt), p.matchTokenKind(TokenKindString), p.matchKeyword("NULL"):
 		return p.parseLiteral(p.Pos())
 	default:
-		return nil, fmt.Errorf("unexpected token: %q, expected <Ident>, <literal>", p.last().String)
+		return nil, fmt.Errorf("unexpected token: %q, expected <Name>, <literal>", p.last().String)
 	}
 }
 


Test patch:
diff --git a/parser/lexer_test.go b/parser/lexer_test.go
index 5b04f8f..9cb2e86 100644
--- a/parser/lexer_test.go
+++ b/parser/lexer_test.go
@@ -144,7 +144,7 @@ func TestConsumeNumber(t *testing.T) {
 		}
 	})
 
-	t.Run("Ident", func(t *testing.T) {
+	t.Run("Name", func(t *testing.T) {
 		idents := []string{
 			"`CASE`",
 			"`TEST`",
diff --git a/parser/testdata/ddl/create_table_basic.sql b/parser/testdata/ddl/create_table_basic.sql
index 5969526..ce8fe1c 100644
--- a/parser/testdata/ddl/create_table_basic.sql
+++ b/parser/testdata/ddl/create_table_basic.sql
@@ -22,6 +22,7 @@ CREATE TABLE IF NOT EXISTS test.events_local (
     f8 Datetime DEFAULT now(),
     f9 String MATERIALIZED toString(f7['f70']),
     f10 String ALIAS f11,
+    f12 JSON(SKIP a, SKIP a.b.c, SKIP REGEXP 'hello'),
 ) ENGINE = MergeTree
 PRIMARY KEY (f0, f1, f2)
 PARTITION BY toYYYYMMDD(f3)
diff --git a/parser/testdata/ddl/format/create_table_basic.sql b/parser/testdata/ddl/format/create_table_basic.sql
index 3263d6c..2b5be17 100644
--- a/parser/testdata/ddl/format/create_table_basic.sql
+++ b/parser/testdata/ddl/format/create_table_basic.sql
@@ -23,6 +23,7 @@ CREATE TABLE IF NOT EXISTS test.events_local (
     f8 Datetime DEFAULT now(),
     f9 String MATERIALIZED toString(f7['f70']),
     f10 String ALIAS f11,
+    f12 JSON(SKIP a, SKIP a.b.c, SKIP REGEXP 'hello'),
 ) ENGINE = MergeTree
 PRIMARY KEY (f0, f1, f2)
 PARTITION BY toYYYYMMDD(f3)
@@ -31,4 +32,4 @@ ORDER BY (f1,f2,f3)
 COMMENT 'Comment for table';
 
 -- Format SQL:
-CREATE TABLE IF NOT EXISTS test.events_local (f0 String, f1 String CODEC(ZSTD(1)), f2 VARCHAR(255), f3 Datetime, f4 Datetime, f5 Map(String, String), f6 String, f7 Nested(f70 UInt32, f71 UInt32, f72 DateTime, f73 Int64, f74 Int64, f75 String), f8 Datetime DEFAULT now(), f9 String MATERIALIZED toString(f7['f70']), f10 String ALIAS f11) ENGINE = MergeTree PRIMARY KEY (f0, f1, f2) PARTITION BY toYYYYMMDD(f3) TTL f3 + INTERVAL 6 MONTH ORDER BY (f1, f2, f3) COMMENT 'Comment for table';
+CREATE TABLE IF NOT EXISTS test.events_local (f0 String, f1 String CODEC(ZSTD(1)), f2 VARCHAR(255), f3 Datetime, f4 Datetime, f5 Map(String, String), f6 String, f7 Nested(f70 UInt32, f71 UInt32, f72 DateTime, f73 Int64, f74 Int64, f75 String), f8 Datetime DEFAULT now(), f9 String MATERIALIZED toString(f7['f70']), f10 String ALIAS f11, f12 JSON(SKIP a, SKIP a.b.c,  SKIP REGEXP 'hello')) ENGINE = MergeTree PRIMARY KEY (f0, f1, f2) PARTITION BY toYYYYMMDD(f3) TTL f3 + INTERVAL 6 MONTH ORDER BY (f1, f2, f3) COMMENT 'Comment for table';
diff --git a/parser/testdata/ddl/output/create_table_basic.sql.golden.json b/parser/testdata/ddl/output/create_table_basic.sql.golden.json
index 6cbb3b5..c87a758 100644
--- a/parser/testdata/ddl/output/create_table_basic.sql.golden.json
+++ b/parser/testdata/ddl/output/create_table_basic.sql.golden.json
@@ -1,7 +1,7 @@
 [
   {
     "CreatePos": 122,
-    "StatementEnd": 676,
+    "StatementEnd": 731,
     "Name": {
       "Database": {
         "Name": "test",
@@ -21,7 +21,7 @@
     "OnCluster": null,
     "TableSchema": {
       "SchemaPos": 167,
-      "SchemaEnd": 558,
+      "SchemaEnd": 613,
       "Columns": [
         {
           "NamePos": 173,
@@ -655,32 +655,114 @@
           "TTL": null,
           "Comment": null,
           "CompressionCodec": null
+        },
+        {
+          "NamePos": 562,
+          "ColumnEnd": 610,
+          "Name": {
+            "Ident": {
+              "Name": "f12",
+              "QuoteType": 1,
+              "NamePos": 562,
+              "NameEnd": 565
+            },
+            "DotIdent": null
+          },
+          "Type": {
+            "Name": {
+              "Name": "JSON",
+              "QuoteType": 1,
+              "NamePos": 566,
+              "NameEnd": 570
+            },
+            "Options": {
+              "LParen": 571,
+              "RParen": 610,
+              "Items": [
+                {
+                  "SkipPath": {
+                    "Idents": [
+                      {
+                        "Name": "a",
+                        "QuoteType": 1,
+                        "NamePos": 576,
+                        "NameEnd": 577
+                      }
+                    ]
+                  },
+                  "SkipRegex": null
+                },
+                {
+                  "SkipPath": {
+                    "Idents": [
+                      {
+                        "Name": "a",
+                        "QuoteType": 1,
+                        "NamePos": 584,
+                        "NameEnd": 585
+                      },
+                      {
+                        "Name": "b",
+                        "QuoteType": 1,
+                        "NamePos": 586,
+                        "NameEnd": 587
+                      },
+                      {
+                        "Name": "c",
+                        "QuoteType": 1,
+                        "NamePos": 588,
+                        "NameEnd": 589
+                      }
+                    ]
+                  },
+                  "SkipRegex": null
+                },
+                {
+                  "SkipPath": null,
+                  "SkipRegex": {
+                    "LiteralPos": 604,
+                    "LiteralEnd": 609,
+                    "Literal": "hello"
+                  }
+                }
+              ]
+            }
+          },
+          "NotNull": null,
+          "Nullable": null,
+          "DefaultExpr": null,
+          "MaterializedExpr": null,
+          "AliasExpr": null,
+          "Codec": null,
+          "TTL": null,
+          "Comment": null,
+          "CompressionCodec": null
         }
       ],
       "AliasTable": null,
       "TableFunction": null
     },
     "Engine": {
-      "EnginePos": 560,
-      "EngineEnd": 676,
+      "EnginePos": 615,
+      "EngineEnd": 731,
       "Name": "MergeTree",
       "Params": null,
       "PrimaryKey": {
-        "PrimaryPos": 579,
+        "PrimaryPos": 634,
         "Expr": {
-          "LeftParenPos": 591,
-          "RightParenPos": 602,
+          "LeftParenPos": 646,
+          "RightParenPos": 657,
           "Items": {
-            "ListPos": 592,
-            "ListEnd": 602,
+            "ListPos": 647,
+            "ListEnd": 657,
             "HasDistinct": false,
             "Items": [
               {
                 "Expr": {
                   "Name": "f0",
                   "QuoteType": 1,
-                  "NamePos": 592,
-                  "NameEnd": 594
+                  "NamePos": 647,
+                  "NameEnd": 649
                 },
                 "Alias": null
               },
@@ -688,8 +770,8 @@
                 "Expr": {
                   "Name": "f1",
                   "QuoteType": 1,
-                  "NamePos": 596,
-                  "NameEnd": 598
+                  "NamePos": 651,
+                  "NameEnd": 653
                 },
                 "Alias": null
               },
@@ -697,8 +779,8 @@
                 "Expr": {
                   "Name": "f2",
                   "QuoteType": 1,
-                  "NamePos": 600,
-                  "NameEnd": 602
+                  "NamePos": 655,
+                  "NameEnd": 657
                 },
                 "Alias": null
               }
@@ -708,10 +790,10 @@
         }
       },
       "PartitionBy": {
-        "PartitionPos": 604,
+        "PartitionPos": 659,
         "Expr": {
-          "ListPos": 617,
-          "ListEnd": 630,
+          "ListPos": 672,
+          "ListEnd": 685,
           "HasDistinct": false,
           "Items": [
             {
@@ -719,23 +801,23 @@
                 "Name": {
                   "Name": "toYYYYMMDD",
                   "QuoteType": 1,
-                  "NamePos": 617,
-                  "NameEnd": 627
+                  "NamePos": 672,
+                  "NameEnd": 682
                 },
                 "Params": {
-                  "LeftParenPos": 627,
-                  "RightParenPos": 630,
+                  "LeftParenPos": 682,
+                  "RightParenPos": 685,
                   "Items": {
-                    "ListPos": 628,
-                    "ListEnd": 630,
+                    "ListPos": 683,
+                    "ListEnd": 685,
                     "HasDistinct": false,
                     "Items": [
                       {
                         "Expr": {
                           "Name": "f3",
                           "QuoteType": 1,
-                          "NamePos": 628,
-                          "NameEnd": 630
+                          "NamePos": 683,
+                          "NameEnd": 685
                         },
                         "Alias": null
                       }
@@ -751,32 +833,32 @@
       },
       "SampleBy": null,
       "TTL": {
-        "TTLPos": 632,
-        "ListEnd": 657,
+        "TTLPos": 687,
+        "ListEnd": 712,
         "Items": [
           {
-            "TTLPos": 632,
+            "TTLPos": 687,
             "Expr": {
               "LeftExpr": {
                 "Name": "f3",
                 "QuoteType": 1,
-                "NamePos": 636,
-                "NameEnd": 638
+                "NamePos": 691,
+                "NameEnd": 693
               },
               "Operation": "+",
               "RightExpr": {
-                "IntervalPos": 641,
+                "IntervalPos": 696,
                 "Expr": {
-                  "NumPos": 650,
-                  "NumEnd": 651,
+                  "NumPos": 705,
+                  "NumEnd": 706,
                   "Literal": "6",
                   "Base": 10
                 },
                 "Unit": {
                   "Name": "MONTH",
                   "QuoteType": 1,
-                  "NamePos": 652,
-                  "NameEnd": 657
+                  "NamePos": 707,
+                  "NameEnd": 712
                 }
               },
               "HasGlobal": false,
@@ -787,25 +869,25 @@
       },
       "Settings": null,
       "OrderBy": {
-        "OrderPos": 658,
-        "ListEnd": 676,
+        "OrderPos": 713,
+        "ListEnd": 731,
         "Items": [
           {
-            "OrderPos": 658,
+            "OrderPos": 713,
             "Expr": {
-              "LeftParenPos": 667,
-              "RightParenPos": 676,
+              "LeftParenPos": 722,
+              "RightParenPos": 731,
               "Items": {
-                "ListPos": 668,
-                "ListEnd": 676,
+                "ListPos": 723,
+                "ListEnd": 731,
                 "HasDistinct": false,
                 "Items": [
                   {
                     "Expr": {
                       "Name": "f1",
                       "QuoteType": 1,
-                      "NamePos": 668,
-                      "NameEnd": 670
+                      "NamePos": 723,
+                      "NameEnd": 725
                     },
                     "Alias": null
                   },
@@ -813,8 +895,8 @@
                     "Expr": {
                       "Name": "f2",
                       "QuoteType": 1,
-                      "NamePos": 671,
-                      "NameEnd": 673
+                      "NamePos": 726,
+                      "NameEnd": 728
                     },
                     "Alias": null
                   },
@@ -822,8 +904,8 @@
                     "Expr": {
                       "Name": "f3",
                       "QuoteType": 1,
-                      "NamePos": 674,
-                      "NameEnd": 676
+                      "NamePos": 729,
+                      "NameEnd": 731
                     },
                     "Alias": null
                   }
@@ -840,8 +922,8 @@
     "SubQuery": null,
     "HasTemporary": false,
     "Comment": {
-      "LiteralPos": 687,
-      "LiteralEnd": 704,
+      "LiteralPos": 742,
+      "LiteralEnd": 759,
       "Literal": "Comment for table"
     }
   }