Repository: AfterShip/clickhouse-sql-parser (go)

Issue:
Support JSON parameter for `max_dynamic_paths` and `max_dynamic_types`
## Background
Currently, the table column type support JSON with two specify parameter which name is `max_dynamic_paths` and `max_dynamic_types`. 

For some specify scenarios, both of the parameter can help us to make the prevention of avalanche for column data files on disk, as the below reference Blob said.

## What Problem I met
DDL
```sql
CREATE TABLE IF NOT EXISTS test.events_local (
    f0 String,
    f12 JSON(max_dynamic_types=10, max_dynamic_paths=3),
) ENGINE = MergeTree
PRIMARY KEY (f0)
PARTITION BY f0
ORDER BY (f0)
```

result: do not support json two specify parameter

<img width="915" alt="Image" src="https://github.com/user-attachments/assets/bcb512d1-0cb5-43a7-92e9-94c2101ed67e" />

## How to Support
* AST add two specify parameter with type `*NumberLiteral` into `JSONOption`
* Assert JSON the parameters and Rewrite SQL into `JSONOption` self-method `func(*JSONOption)String()`
* Add `parseJSONMaxDynamicOptions` to enhance JSON options parser

## Reference
* [Column Type JSON Document](https://clickhouse.com/docs/sql-reference/data-types/newjson) 
* [Blob: How we built a new powerful JSON data type for ClickHouse](https://clickhouse.com/blog/a-new-powerful-json-data-type-for-clickhouse)
* [What is a reasonable maximum for JSON's max_dynamic_paths](https://github.com/ClickHouse/ClickHouse/discussions/76106)

Pull request:
Support JSON parameter for `max_dynamic_paths` and `max_dynamic_types`
## Background
close #163 

Patch:
diff --git a/parser/ast.go b/parser/ast.go
index 0196511..01960b4 100644
--- a/parser/ast.go
+++ b/parser/ast.go
@@ -3716,8 +3716,10 @@ func (j *JSONPath) String() string {
 }
 
 type JSONOption struct {
-	SkipPath  *JSONPath
-	SkipRegex *StringLiteral
+	SkipPath        *JSONPath
+	SkipRegex       *StringLiteral
+	MaxDynamicPaths *NumberLiteral
+	MaxDynamicTypes *NumberLiteral
 }
 
 func (j *JSONOption) String() string {
@@ -3730,6 +3732,16 @@ func (j *JSONOption) String() string {
 		builder.WriteString(" SKIP REGEXP ")
 		builder.WriteString(j.SkipRegex.String())
 	}
+	if j.MaxDynamicPaths != nil {
+		builder.WriteString("max_dynamic_paths")
+		builder.WriteByte('=')
+		builder.WriteString(j.MaxDynamicPaths.String())
+	}
+	if j.MaxDynamicTypes != nil {
+		builder.WriteString("max_dynamic_types")
+		builder.WriteByte('=')
+		builder.WriteString(j.MaxDynamicTypes.String())
+	}
 	return builder.String()
 }
 
diff --git a/parser/parser_column.go b/parser/parser_column.go
index 42830a8..8cd1999 100644
--- a/parser/parser_column.go
+++ b/parser/parser_column.go
@@ -964,6 +964,34 @@ func (p *Parser) parseJSONPath() (*JSONPath, error) {
 	}, nil
 }
 
+func (p *Parser) parseJSONMaxDynamicOptions(pos Pos) (*JSONOption, error) {
+	ident, err := p.parseIdent()
+	if err != nil {
+		return nil, err
+	}
+
+	if err := p.expectTokenKind(TokenKindSingleEQ); err != nil {
+		return nil, err
+	}
+
+	switch ident.Name {
+	case "max_dynamic_types":
+		number, err := p.parseNumber(pos)
+		if err != nil {
+			return nil, err
+		}
+		return &JSONOption{MaxDynamicTypes: number}, nil
+	case "max_dynamic_paths":
+		number, err := p.parseNumber(pos)
+		if err != nil {
+			return nil, err
+		}
+		return &JSONOption{MaxDynamicPaths: number}, nil
+	default:
+		return nil, fmt.Errorf("unexpected token kind: %s", p.lastTokenKind())
+	}
+}
+
 func (p *Parser) parseJSONOption() (*JSONOption, error) {
 	switch {
 	case p.tryConsumeKeywords(KeywordSkip):
@@ -983,6 +1011,8 @@ func (p *Parser) parseJSONOption() (*JSONOption, error) {
 		return &JSONOption{
 			SkipPath: jsonPath,
 		}, nil
+	case p.matchTokenKind(TokenKindIdent):
+		return p.parseJSONMaxDynamicOptions(p.Pos())
 	default:
 		return nil, fmt.Errorf("unexpected token kind: %s", p.lastTokenKind())
 	}


Test patch:
diff --git a/parser/testdata/ddl/create_table_basic.sql b/parser/testdata/ddl/create_table_basic.sql
index ce8fe1c..493d427 100644
--- a/parser/testdata/ddl/create_table_basic.sql
+++ b/parser/testdata/ddl/create_table_basic.sql
@@ -22,7 +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'),
+    f12 JSON(max_dynamic_types=10, max_dynamic_paths=3, 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 2b5be17..dcf71a4 100644
--- a/parser/testdata/ddl/format/create_table_basic.sql
+++ b/parser/testdata/ddl/format/create_table_basic.sql
@@ -23,7 +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'),
+    f12 JSON(max_dynamic_types=10, max_dynamic_paths=3, SKIP a, SKIP a.b.c, SKIP REGEXP 'hello'),
 ) ENGINE = MergeTree
 PRIMARY KEY (f0, f1, f2)
 PARTITION BY toYYYYMMDD(f3)
@@ -32,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, 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';
+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(max_dynamic_types=10, max_dynamic_paths=3, 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 8d6e831..5cb87b3 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": 731,
+    "StatementEnd": 774,
     "OrReplace": false,
     "Name": {
       "Database": {
@@ -22,7 +22,7 @@
     "OnCluster": null,
     "TableSchema": {
       "SchemaPos": 167,
-      "SchemaEnd": 613,
+      "SchemaEnd": 656,
       "Columns": [
         {
           "NamePos": 173,
@@ -659,7 +659,7 @@
         },
         {
           "NamePos": 562,
-          "ColumnEnd": 610,
+          "ColumnEnd": 653,
           "Name": {
             "Ident": {
               "Name": "f12",
@@ -678,20 +678,44 @@
             },
             "Options": {
               "LParen": 571,
-              "RParen": 610,
+              "RParen": 653,
               "Items": [
+                {
+                  "SkipPath": null,
+                  "SkipRegex": null,
+                  "MaxDynamicPaths": null,
+                  "MaxDynamicTypes": {
+                    "NumPos": 571,
+                    "NumEnd": 591,
+                    "Literal": "10",
+                    "Base": 10
+                  }
+                },
+                {
+                  "SkipPath": null,
+                  "SkipRegex": null,
+                  "MaxDynamicPaths": {
+                    "NumPos": 593,
+                    "NumEnd": 612,
+                    "Literal": "3",
+                    "Base": 10
+                  },
+                  "MaxDynamicTypes": null
+                },
                 {
                   "SkipPath": {
                     "Idents": [
                       {
                         "Name": "a",
                         "QuoteType": 1,
-                        "NamePos": 576,
-                        "NameEnd": 577
+                        "NamePos": 619,
+                        "NameEnd": 620
                       }
                     ]
                   },
-                  "SkipRegex": null
+                  "SkipRegex": null,
+                  "MaxDynamicPaths": null,
+                  "MaxDynamicTypes": null
                 },
                 {
                   "SkipPath": {
@@ -699,32 +723,36 @@
                       {
                         "Name": "a",
                         "QuoteType": 1,
-                        "NamePos": 584,
-                        "NameEnd": 585
+                        "NamePos": 627,
+                        "NameEnd": 628
                       },
                       {
                         "Name": "b",
                         "QuoteType": 1,
-                        "NamePos": 586,
-                        "NameEnd": 587
+                        "NamePos": 629,
+                        "NameEnd": 630
                       },
                       {
                         "Name": "c",
                         "QuoteType": 1,
-                        "NamePos": 588,
-                        "NameEnd": 589
+                        "NamePos": 631,
+                        "NameEnd": 632
                       }
                     ]
                   },
-                  "SkipRegex": null
+                  "SkipRegex": null,
+                  "MaxDynamicPaths": null,
+                  "MaxDynamicTypes": null
                 },
                 {
                   "SkipPath": null,
                   "SkipRegex": {
-                    "LiteralPos": 604,
-                    "LiteralEnd": 609,
+                    "LiteralPos": 647,
+                    "LiteralEnd": 652,
                     "Literal": "hello"
-                  }
+                  },
+                  "MaxDynamicPaths": null,
+                  "MaxDynamicTypes": null
                 }
               ]
             }
@@ -744,26 +772,26 @@
       "TableFunction": null
     },
     "Engine": {
-      "EnginePos": 615,
-      "EngineEnd": 731,
+      "EnginePos": 658,
+      "EngineEnd": 774,
       "Name": "MergeTree",
       "Params": null,
       "PrimaryKey": {
-        "PrimaryPos": 634,
+        "PrimaryPos": 677,
         "Expr": {
-          "LeftParenPos": 646,
-          "RightParenPos": 657,
+          "LeftParenPos": 689,
+          "RightParenPos": 700,
           "Items": {
-            "ListPos": 647,
-            "ListEnd": 657,
+            "ListPos": 690,
+            "ListEnd": 700,
             "HasDistinct": false,
             "Items": [
               {
                 "Expr": {
                   "Name": "f0",
                   "QuoteType": 1,
-                  "NamePos": 647,
-                  "NameEnd": 649
+                  "NamePos": 690,
+                  "NameEnd": 692
                 },
                 "Alias": null
               },
@@ -771,8 +799,8 @@
                 "Expr": {
                   "Name": "f1",
                   "QuoteType": 1,
-                  "NamePos": 651,
-                  "NameEnd": 653
+                  "NamePos": 694,
+                  "NameEnd": 696
                 },
                 "Alias": null
               },
@@ -780,8 +808,8 @@
                 "Expr": {
                   "Name": "f2",
                   "QuoteType": 1,
-                  "NamePos": 655,
-                  "NameEnd": 657
+                  "NamePos": 698,
+                  "NameEnd": 700
                 },
                 "Alias": null
               }
@@ -791,10 +819,10 @@
         }
       },
       "PartitionBy": {
-        "PartitionPos": 659,
+        "PartitionPos": 702,
         "Expr": {
-          "ListPos": 672,
-          "ListEnd": 685,
+          "ListPos": 715,
+          "ListEnd": 728,
           "HasDistinct": false,
           "Items": [
             {
@@ -802,23 +830,23 @@
                 "Name": {
                   "Name": "toYYYYMMDD",
                   "QuoteType": 1,
-                  "NamePos": 672,
-                  "NameEnd": 682
+                  "NamePos": 715,
+                  "NameEnd": 725
                 },
                 "Params": {
-                  "LeftParenPos": 682,
-                  "RightParenPos": 685,
+                  "LeftParenPos": 725,
+                  "RightParenPos": 728,
                   "Items": {
-                    "ListPos": 683,
-                    "ListEnd": 685,
+                    "ListPos": 726,
+                    "ListEnd": 728,
                     "HasDistinct": false,
                     "Items": [
                       {
                         "Expr": {
                           "Name": "f3",
                           "QuoteType": 1,
-                          "NamePos": 683,
-                          "NameEnd": 685
+                          "NamePos": 726,
+                          "NameEnd": 728
                         },
                         "Alias": null
                       }
@@ -834,32 +862,32 @@
       },
       "SampleBy": null,
       "TTL": {
-        "TTLPos": 687,
-        "ListEnd": 712,
+        "TTLPos": 730,
+        "ListEnd": 755,
         "Items": [
           {
-            "TTLPos": 687,
+            "TTLPos": 730,
             "Expr": {
               "LeftExpr": {
                 "Name": "f3",
                 "QuoteType": 1,
-                "NamePos": 691,
-                "NameEnd": 693
+                "NamePos": 734,
+                "NameEnd": 736
               },
               "Operation": "+",
               "RightExpr": {
-                "IntervalPos": 696,
+                "IntervalPos": 739,
                 "Expr": {
-                  "NumPos": 705,
-                  "NumEnd": 706,
+                  "NumPos": 748,
+                  "NumEnd": 749,
                   "Literal": "6",
                   "Base": 10
                 },
                 "Unit": {
                   "Name": "MONTH",
                   "QuoteType": 1,
-                  "NamePos": 707,
-                  "NameEnd": 712
+                  "NamePos": 750,
+                  "NameEnd": 755
                 }
               },
               "HasGlobal": false,
@@ -871,25 +899,25 @@
       },
       "Settings": null,
       "OrderBy": {
-        "OrderPos": 713,
-        "ListEnd": 731,
+        "OrderPos": 756,
+        "ListEnd": 774,
         "Items": [
           {
-            "OrderPos": 713,
+            "OrderPos": 756,
             "Expr": {
-              "LeftParenPos": 722,
-              "RightParenPos": 731,
+              "LeftParenPos": 765,
+              "RightParenPos": 774,
               "Items": {
-                "ListPos": 723,
-                "ListEnd": 731,
+                "ListPos": 766,
+                "ListEnd": 774,
                 "HasDistinct": false,
                 "Items": [
                   {
                     "Expr": {
                       "Name": "f1",
                       "QuoteType": 1,
-                      "NamePos": 723,
-                      "NameEnd": 725
+                      "NamePos": 766,
+                      "NameEnd": 768
                     },
                     "Alias": null
                   },
@@ -897,8 +925,8 @@
                     "Expr": {
                       "Name": "f2",
                       "QuoteType": 1,
-                      "NamePos": 726,
-                      "NameEnd": 728
+                      "NamePos": 769,
+                      "NameEnd": 771
                     },
                     "Alias": null
                   },
@@ -906,8 +934,8 @@
                     "Expr": {
                       "Name": "f3",
                       "QuoteType": 1,
-                      "NamePos": 729,
-                      "NameEnd": 731
+                      "NamePos": 772,
+                      "NameEnd": 774
                     },
                     "Alias": null
                   }
@@ -924,8 +952,8 @@
     "SubQuery": null,
     "HasTemporary": false,
     "Comment": {
-      "LiteralPos": 742,
-      "LiteralEnd": 759,
+      "LiteralPos": 785,
+      "LiteralEnd": 802,
       "Literal": "Comment for table"
     }
   }