[feat] Fixed onError for Oracle, added onEmptyClause, addedParsingTyp…

…e, formatJson for Body
JSQLParser · manticore-projects · Apr 2, 2026 · Oct 13, 2025 · Oct 15, 2025 · Oct 17, 2025
commit 3908529d790231e62bb4bee03957d48cc39cb337
diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonTableFunction.java b/src/main/java/net/sf/jsqlparser/expression/JsonTableFunction.java
@@ -36,6 +36,14 @@ public enum JsonTableOnErrorType {
         ERROR, NULL, EMPTY
     }
 
+    public enum JsonTableOnEmptyType {
+        ERROR, NULL, EMPTY
+    }
+
+    public enum JsonTableParsingType {
+        STRICT, LAX
+    }
+
     public static class JsonTablePassingClause extends ASTNodeAccessImpl implements Serializable {
         private Expression valueExpression;
         private String parameterName;
@@ -78,10 +86,28 @@ public String toString() {
     }
 
     public static class JsonTableWrapperClause extends ASTNodeAccessImpl implements Serializable {
+        private boolean beforePathExpression;
         private JsonFunction.JsonWrapperType wrapperType;
         private JsonFunction.JsonWrapperMode wrapperMode;
         private boolean array;
 
+        /**
+         * Creates a wrapper clause. Depending on the dialect, this clause can come before or after the PATH expression.
+         * <ul>
+         *     <li>Trino: after PATH</li>
+         *     <li>Oracle: before PATH</li>
+         * </ul>
+         *
+         * @param beforePathExpression A flag to determine wether the clause is rendered before or after the PATH expression
+         */
+        public JsonTableWrapperClause(boolean beforePathExpression) {
+            this.beforePathExpression = beforePathExpression;
+        }
+
+        public boolean isBeforePathExpression() {
+            return beforePathExpression;
+        }
+
         public JsonFunction.JsonWrapperType getWrapperType() {
             return wrapperType;
         }
@@ -159,6 +185,15 @@ public String toString() {
 
     public static class JsonTableOnErrorClause extends ASTNodeAccessImpl implements Serializable {
         private JsonTableOnErrorType type;
+        private boolean beforeColumns = true;
+
+        public JsonTableOnErrorClause(boolean beforeColumns) {
+            this.beforeColumns = beforeColumns;
+        }
+
+        public boolean isBeforeColumns() {
+            return beforeColumns;
+        }
 
         public JsonTableOnErrorType getType() {
             return type;
@@ -175,6 +210,48 @@ public String toString() {
         }
     }
 
+    public static class JsonTableOnEmptyClause extends ASTNodeAccessImpl implements Serializable {
+        private JsonTableOnEmptyType type;
+
+        public JsonTableOnEmptyClause() {
+        }
+
+        public JsonTableOnEmptyType getType() {
+            return type;
+        }
+
+        public JsonTableOnEmptyClause setType(JsonTableOnEmptyType type) {
+            this.type = type;
+            return this;
+        }
+
+        @Override
+        public String toString() {
+            return type + " ON EMPTY";
+        }
+    }
+
+    public static class JsonTableParsingTypeClause extends ASTNodeAccessImpl implements Serializable {
+        private JsonTableParsingType type;
+
+        public JsonTableParsingTypeClause() {
+        }
+
+        public JsonTableParsingType getType() {
+            return type;
+        }
+
+        public JsonTableParsingTypeClause setType(JsonTableParsingType type) {
+            this.type = type;
+            return this;
+        }
+
+        @Override
+        public String toString() {
+            return "TYPE(" + type + ")";
+        }
+    }
+
     public static class JsonTablePlanTerm extends ASTNodeAccessImpl implements Serializable {
         private JsonTablePlanExpression nestedPlanExpression;
         private String name;
@@ -532,10 +609,13 @@ public String toString() {
                 builder.append(scalarsType);
                 builder.append(" SCALARS");
             }
+            if (wrapperClause != null && wrapperClause.isBeforePathExpression()) {
+                builder.append(" ").append(wrapperClause);
+            }
             if (pathExpression != null) {
                 builder.append(" PATH ").append(pathExpression);
             }
-            if (wrapperClause != null) {
+            if (wrapperClause != null && !wrapperClause.isBeforePathExpression()) {
                 builder.append(" ").append(wrapperClause);
             }
             if (quotesClause != null) {
@@ -595,11 +675,23 @@ public String toString() {
     private JsonTableColumnsClause columnsClause;
     private JsonTablePlanClause planClause;
     private JsonTableOnErrorClause onErrorClause;
+    private JsonTableParsingTypeClause parsingTypeClause;
+    private JsonTableOnEmptyClause onEmptyClause;
+    private boolean formatJson;
 
     public JsonTableFunction() {
         setName("JSON_TABLE");
     }
 
+    public boolean getFormatJson() {
+        return formatJson;
+    }
+
+    public JsonTableFunction setFormatJson(boolean formatJson) {
+        this.formatJson = formatJson;
+        return this;
+    }
+
     public Expression getJsonInputExpression() {
         return jsonInputExpression;
     }
@@ -663,6 +755,24 @@ public JsonTableFunction setOnErrorClause(JsonTableOnErrorClause onErrorClause)
         return this;
     }
 
+    public JsonTableParsingTypeClause getParsingTypeClause() {
+        return parsingTypeClause;
+    }
+
+    public JsonTableFunction setParsingTypeClause(JsonTableParsingTypeClause parsingTypeClause) {
+        this.parsingTypeClause = parsingTypeClause;
+        return this;
+    }
+
+    public JsonTableOnEmptyClause getOnEmptyClause() {
+        return onEmptyClause;
+    }
+
+    public JsonTableFunction setOnEmptyClause(JsonTableOnEmptyClause onEmptyClause) {
+        this.onEmptyClause = onEmptyClause;
+        return this;
+    }
+
     public List<Expression> getAllExpressions() {
         List<Expression> expressions = new ArrayList<>();
         if (jsonInputExpression != null) {
@@ -692,6 +802,9 @@ public <T, S> T accept(ExpressionVisitor<T> expressionVisitor, S context) {
     public String toString() {
         StringBuilder builder = new StringBuilder("JSON_TABLE(");
         builder.append(jsonInputExpression);
+        if (formatJson) {
+            builder.append(" FORMAT JSON");
+        }
         if (jsonPathExpression != null) {
             builder.append(", ").append(jsonPathExpression);
         }
@@ -709,11 +822,20 @@ public String toString() {
                 first = false;
             }
         }
+        if (onErrorClause != null && onErrorClause.isBeforeColumns()) {
+            builder.append(" ").append(onErrorClause);
+        }
+        if (parsingTypeClause != null) {
+            builder.append(" ").append(parsingTypeClause);
+        }
+        if (onEmptyClause != null) {
+            builder.append(" ").append(onEmptyClause);
+        }
         builder.append(" ").append(columnsClause);
         if (planClause != null) {
             builder.append(" ").append(planClause);
         }
-        if (onErrorClause != null) {
+        if (onErrorClause != null && !onErrorClause.isBeforeColumns()) {
             builder.append(" ").append(onErrorClause);
         }
         builder.append(")");

diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -926,6 +926,7 @@ String NonReservedWord() :
       | tk=<K_KILL:"KILL">
       | tk=<K_FN:"FN">
       | tk=<K_LAST: "LAST">
+      | tk=<K_LAX: "LAX">
       | tk=<K_LEADING:"LEADING">
       | tk=<K_LESS:"LESS">
       | tk=<K_LEVEL:"LEVEL">
@@ -9419,9 +9420,9 @@ JsonFunction.JsonOnResponseBehavior JsonTableOnEmptyBehavior() : {
     }
 }
 
-JsonTableFunction.JsonTableWrapperClause JsonTableWrapperClause() : {
+JsonTableFunction.JsonTableWrapperClause JsonTableWrapperClause(boolean beforePathExpr) : {
     JsonTableFunction.JsonTableWrapperClause wrapperClause =
-            new JsonTableFunction.JsonTableWrapperClause();
+            new JsonTableFunction.JsonTableWrapperClause(beforePathExpr);
     Token token;
 }
 {
@@ -9508,7 +9509,7 @@ JsonTableFunction.JsonTableColumnDefinition JsonTableColumnDefinition() : {
             columnDefinition = valueColumnDefinition;
         }
         (
-            <K_FOR> JsonKeyword("ORDINALITY") { valueColumnDefinition.setForOrdinality(true); }
+            <K_FOR> <K_ORDINALITY> { valueColumnDefinition.setForOrdinality(true); }
             |
             [ dataType = ColDataType() { valueColumnDefinition.setDataType(dataType); } ]
             [
@@ -9523,8 +9524,11 @@ JsonTableFunction.JsonTableColumnDefinition JsonTableColumnDefinition() : {
                 )
                 JsonKeyword("SCALARS")
             ]
+            // In Oracle, the wrapper clause comes before the PATH expression
+            [ wrapperClause = JsonTableWrapperClause(true) { valueColumnDefinition.setWrapperClause(wrapperClause); } ]
             [ <K_PATH> expression = Expression() { valueColumnDefinition.setPathExpression(expression); } ]
-            [ wrapperClause = JsonTableWrapperClause() { valueColumnDefinition.setWrapperClause(wrapperClause); } ]
+            // In Truno the wrapper clause comes after the PATH expression
+            [ wrapperClause = JsonTableWrapperClause(false) { valueColumnDefinition.setWrapperClause(wrapperClause); } ]
             [
                 LOOKAHEAD({
                     getToken(1).kind == K_KEEP
@@ -9657,16 +9661,18 @@ JsonTableFunction.JsonTablePlanClause JsonTablePlanClause() : {
     }
 }
 
-JsonTableFunction.JsonTableOnErrorClause JsonTableOnErrorClause() : {
+JsonTableFunction.JsonTableOnErrorClause JsonTableOnErrorClause(boolean beforeColumns) : {
     JsonTableFunction.JsonTableOnErrorClause onErrorClause =
-            new JsonTableFunction.JsonTableOnErrorClause();
+            new JsonTableFunction.JsonTableOnErrorClause(beforeColumns);
     Token token;
 }
 {
     (
         <K_ERROR> { onErrorClause.setType(JsonTableFunction.JsonTableOnErrorType.ERROR); }
         |
         <K_EMPTY> { onErrorClause.setType(JsonTableFunction.JsonTableOnErrorType.EMPTY); }
+        |
+        <K_NULL> { onErrorClause.setType(JsonTableFunction.JsonTableOnErrorType.NULL); }
     )
     <K_ON> <K_ERROR>
     {
@@ -9676,6 +9682,44 @@ JsonTableFunction.JsonTableOnErrorClause JsonTableOnErrorClause() : {
     }
 }
 
+JsonTableFunction.JsonTableOnEmptyClause JsonTableOnEmptyClause() : {
+    JsonTableFunction.JsonTableOnEmptyClause onEmptyClause =
+            new JsonTableFunction.JsonTableOnEmptyClause();
+    Token token;
+}
+{
+    (
+        <K_ERROR> { onEmptyClause.setType(JsonTableFunction.JsonTableOnEmptyType.ERROR); }
+        |
+        <K_EMPTY> { onEmptyClause.setType(JsonTableFunction.JsonTableOnEmptyType.EMPTY); }
+        |
+        <K_NULL> { onEmptyClause.setType(JsonTableFunction.JsonTableOnEmptyType.NULL); }
+    )
+    <K_ON> <K_EMPTY>
+    {
+        if (onEmptyClause.getType() != null) {
+            return onEmptyClause;
+        }
+    }
+}
+
+JsonTableFunction.JsonTableParsingTypeClause JsonTableParsingTypeClause() : {
+    JsonTableFunction.JsonTableParsingTypeClause parsingType = new JsonTableFunction.JsonTableParsingTypeClause();
+}
+{
+    <K_TYPE>
+    <OPENING_BRACKET>
+    (
+        <K_STRICT> { parsingType.setType(JsonTableFunction.JsonTableParsingType.STRICT); }
+        |
+        <K_LAX>  { parsingType.setType(JsonTableFunction.JsonTableParsingType.LAX); }
+    )
+    <CLOSING_BRACKET>
+    {
+        return parsingType;
+    }
+}
+
 JsonTableFunction JsonTableBody() : {
     JsonTableFunction function = new JsonTableFunction();
     Expression jsonInput;
@@ -9685,12 +9729,15 @@ JsonTableFunction JsonTableBody() : {
     JsonTableFunction.JsonTableColumnsClause columnsClause;
     JsonTableFunction.JsonTablePlanClause planClause = null;
     JsonTableFunction.JsonTableOnErrorClause onErrorClause = null;
+    JsonTableFunction.JsonTableParsingTypeClause parsingTypeClause = null;
+    JsonTableFunction.JsonTableOnEmptyClause onEmptyClause = null;
 }
 {
     "("
     jsonInput = Expression() {
         function.setJsonInputExpression(jsonInput);
     }
+    [ <K_FORMAT> <K_JSON> { function.setFormatJson(true); } ]
     [
         ","
         jsonPath = Expression() {
@@ -9708,9 +9755,12 @@ JsonTableFunction JsonTableBody() : {
             passingClause = JsonTablePassingClause() { function.addPassingClause(passingClause); }
         )*
     ]
+    [ LOOKAHEAD(3) onErrorClause = JsonTableOnErrorClause(true) { function.setOnErrorClause(onErrorClause); } ]
+    [ parsingTypeClause = JsonTableParsingTypeClause() { function.setParsingTypeClause(parsingTypeClause); } ]
+    [ onEmptyClause = JsonTableOnEmptyClause() { function.setOnEmptyClause(onEmptyClause); } ]
     columnsClause = JsonTableColumnsClause() { function.setColumnsClause(columnsClause); }
     [ planClause = JsonTablePlanClause() { function.setPlanClause(planClause); } ]
-    [ onErrorClause = JsonTableOnErrorClause() { function.setOnErrorClause(onErrorClause); } ]
+    [ onErrorClause = JsonTableOnErrorClause(false) { function.setOnErrorClause(onErrorClause); } ]
     ")"
     {
         return function;
@@ -10211,6 +10261,7 @@ ColDataType DataType():
             ]
             [
                 LOOKAHEAD(2) "(" ( tk=<S_LONG> { precision = Integer.valueOf(tk.image); } |  tk=<K_MAX> { precision = Integer.MAX_VALUE; } )
+                [ <K_BYTE> | <K_CHAR> ]
                 [ "," tk = <S_LONG> { scale = Integer.valueOf(tk.image); } ]
                 ")"
             ]