[feat] Additional support for JSON_TABLE

JSQLParser · ANeumann82 · Oct 13, 2025 · Oct 15, 2025 · Oct 17, 2025 · Nov 25, 2025
commit 8773eea1f3477e3a04f14c60afcda9d2340dd181
diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonOnEmptyType.java b/src/main/java/net/sf/jsqlparser/expression/JsonOnEmptyType.java
@@ -29,13 +29,8 @@
  *
  */
 public enum JsonOnEmptyType {
-    ERROR("ERROR"),
-    NULL("NULL"),
-    EMPTY("EMPTY"),
-    EMPTY_ARRAY("EMPTY ARRAY"),
-    EMPTY_OBJECT("EMPTY OBJECT"),
-    TRUE("TRUE"),
-    FALSE("FALSE");
+    ERROR("ERROR"), NULL("NULL"), EMPTY("EMPTY"), EMPTY_ARRAY("EMPTY ARRAY"), EMPTY_OBJECT(
+            "EMPTY OBJECT"), TRUE("TRUE"), FALSE("FALSE"), DEFAULT("DEFAULT");
 
     private final String value;
 

diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonOnErrorType.java b/src/main/java/net/sf/jsqlparser/expression/JsonOnErrorType.java
@@ -29,13 +29,8 @@
  *
  */
 public enum JsonOnErrorType {
-    ERROR("ERROR"),
-    NULL("NULL"),
-    EMPTY("EMPTY"),
-    EMPTY_ARRAY("EMPTY ARRAY"),
-    EMPTY_OBJECT("EMPTY OBJECT"),
-    TRUE("TRUE"),
-    FALSE("FALSE");
+    ERROR("ERROR"), NULL("NULL"), EMPTY("EMPTY"), EMPTY_ARRAY("EMPTY ARRAY"), EMPTY_OBJECT(
+            "EMPTY OBJECT"), TRUE("TRUE"), FALSE("FALSE"), DEFAULT("DEFAULT");
 
     private final String value;
 

diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonQueryWrapperType.java b/src/main/java/net/sf/jsqlparser/expression/JsonQueryWrapperType.java
@@ -0,0 +1,74 @@
+/*-
+ * #%L
+ * JSQLParser library
+ * %%
+ * Copyright (C) 2004 - 2021 JSQLParser
+ * %%
+ * Dual licensed under GNU LGPL 2.1 or Apache License 2.0
+ * #L%
+ */
+/*
+ * Copyright (C) 2021 JSQLParser.
+ *
+ * This library is free software; you can redistribute it and/or modify it under the terms of the
+ * GNU Lesser General Public License as published by the Free Software Foundation; either version
+ * 2.1 of the License, or (at your option) any later version.
+ *
+ * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without
+ * even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License along with this library;
+ * if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
+ * 02110-1301 USA
+ */
+
+package net.sf.jsqlparser.expression;
+
+/**
+ *
+ */
+public enum JsonQueryWrapperType {
+    WITHOUT_WRAPPER("WITHOUT WRAPPER"), WITHOUT_ARRAY_WRAPPER(
+            "WITHOUT ARRAY WRAPPER"), WITH_WRAPPER("WITH WRAPPER"), WITH_ARRAY_WRAPPER(
+                    "WITH ARRAY WRAPPER"), WITH_UNCONDITIONAL_WRAPPER(
+                            "WITH UNCONDITIONAL WRAPPER"), WITH_UNCONDITIONAL_ARRAY_WRAPPER(
+                                    "WITH UNCONDITIONAL ARRAY WRAPPER"), WITH_CONDITIONAL_WRAPPER(
+                                            "WITH CONDITIONAL WRAPPER"), WITH_CONDITIONAL_ARRAY_WRAPPER(
+                                                    "WITH CONDITIONAL ARRAY WRAPPER");
+
+    private final String value;
+
+    JsonQueryWrapperType(String value) {
+        this.value = value;
+    }
+
+    public String getValue() {
+        return value;
+    }
+
+    public static JsonQueryWrapperType from(String type) {
+        return Enum.valueOf(JsonQueryWrapperType.class, type.toUpperCase());
+    }
+
+    public static JsonQueryWrapperType fromWithParts(boolean isArray, boolean isConditional,
+            boolean isUnconditional) {
+        if (isArray) {
+            if (isConditional) {
+                return JsonQueryWrapperType.WITH_CONDITIONAL_ARRAY_WRAPPER;
+            } else if (isUnconditional) {
+                return JsonQueryWrapperType.WITH_UNCONDITIONAL_ARRAY_WRAPPER;
+            } else {
+                return JsonQueryWrapperType.WITH_ARRAY_WRAPPER;
+            }
+        } else {
+            if (isConditional) {
+                return JsonQueryWrapperType.WITH_CONDITIONAL_WRAPPER;
+            } else if (isUnconditional) {
+                return JsonQueryWrapperType.WITH_UNCONDITIONAL_WRAPPER;
+            } else {
+                return JsonQueryWrapperType.WITH_WRAPPER;
+            }
+        }
+    }
+}
diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonReturnClause.java b/src/main/java/net/sf/jsqlparser/expression/JsonReturnClause.java
@@ -0,0 +1,71 @@
+package net.sf.jsqlparser.expression;
+
+import net.sf.jsqlparser.parser.ASTNodeAccessImpl;
+
+public class JsonReturnClause extends ASTNodeAccessImpl {
+
+    private JsonReturnType type;
+
+    private Long varcharSize;
+
+    public JsonReturnClause() {}
+
+    public JsonReturnClause(JsonReturnType type) {
+        this.type = type;
+    }
+
+    public JsonReturnType getType() {
+        return type;
+    }
+
+    public void setType(JsonReturnType type) {
+        this.type = type;
+    }
+
+    public JsonReturnClause withType(JsonReturnType type) {
+        setType(type);
+        return this;
+    }
+
+    public Long getVarcharSize() {
+        return varcharSize;
+    }
+
+    public void setVarcharSize(Long varcharSize) {
+        this.varcharSize = varcharSize;
+    }
+
+    public JsonReturnClause withVarcharSize(Long varcharSize) {
+        setVarcharSize(varcharSize);
+        return this;
+    }
+
+    public StringBuilder append(StringBuilder builder) {
+        builder.append(" ");
+        builder.append(type.getValue());
+        switch (type) {
+            case VARCHAR2:
+            case VARCHAR2_BYTE:
+            case VARCHAR2_CHAR:
+                if (varcharSize != null) {
+                    builder.append("(");
+                    builder.append(varcharSize);
+                    switch (type) {
+                        case VARCHAR2_BYTE:
+                            builder.append(" BYTE");
+                            break;
+                        case VARCHAR2_CHAR:
+                            builder.append(" CHAR");
+                            break;
+                    }
+                    builder.append(")");
+                }
+                break;
+            default:
+                // Nothing to do
+                break;
+        }
+        return builder;
+    }
+
+}
diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonReturnType.java b/src/main/java/net/sf/jsqlparser/expression/JsonReturnType.java
@@ -0,0 +1,73 @@
+/*-
+ * #%L
+ * JSQLParser library
+ * %%
+ * Copyright (C) 2004 - 2021 JSQLParser
+ * %%
+ * Dual licensed under GNU LGPL 2.1 or Apache License 2.0
+ * #L%
+ */
+/*
+ * Copyright (C) 2021 JSQLParser.
+ *
+ * This library is free software; you can redistribute it and/or modify it under the terms of the
+ * GNU Lesser General Public License as published by the Free Software Foundation; either version
+ * 2.1 of the License, or (at your option) any later version.
+ *
+ * This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without
+ * even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License along with this library;
+ * if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
+ * 02110-1301 USA
+ */
+
+package net.sf.jsqlparser.expression;
+
+/**
+ *
+ */
+public enum JsonReturnType {
+    VARCHAR2("VARCHAR2"), CLOB("CLOB"), BLOB("BLOB"), NUMBER("NUMBER"), DATE("DATE"), TIMESTAMP(
+            "TIMESTAMP"), TIMESTAMP_WITH_TIMEZONE(
+                    "TIMESTAMP WITH TIMEZONE"), BOOLEAN("BOOLEAN"), VECTOR("VECTOR"), JSON("JSON"),
+
+    // VARCHAR2( x BYTE)
+    VARCHAR2_BYTE("VARCHAR2"),
+
+    // VARCHAR2( x CHAR)
+    VARCHAR2_CHAR("VARCHAR2"),
+    ;
+
+    private final String value;
+
+    JsonReturnType(String value) {
+        this.value = value;
+    }
+
+    public String getValue() {
+        return value;
+    }
+
+    public static JsonReturnType from(String type) {
+        return Enum.valueOf(JsonReturnType.class, type.toUpperCase());
+    }
+
+    /**
+     * @see "https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/JSON_QUERY.html#GUID-6D396EC4-D2AA-43D2-8F5D-08D646A4A2D9__CJADJIIJ"
+     */
+    public boolean isValidForJsonQueryReturnType() {
+        switch (this) {
+            case VARCHAR2:
+            case CLOB:
+            case BLOB:
+            case JSON:
+            case VECTOR:
+                return true;
+            default:
+                return false;
+        }
+    }
+
+}
diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonTable.java b/src/main/java/net/sf/jsqlparser/expression/JsonTable.java
@@ -1,11 +1,14 @@
 package net.sf.jsqlparser.expression;
 
 import net.sf.jsqlparser.parser.ASTNodeAccessImpl;
+import net.sf.jsqlparser.statement.select.AbstractFromitem;
+import net.sf.jsqlparser.statement.select.FromItem;
+import net.sf.jsqlparser.statement.select.FromItemVisitor;
 
 import java.util.ArrayList;
 import java.util.List;
 
-public class JsonTable extends ASTNodeAccessImpl implements Expression {
+public class JsonTable extends AbstractFromitem implements FromItem {
 
     private Expression expression;
     private boolean isFormatJson = false;
@@ -18,7 +21,6 @@ public class JsonTable extends ASTNodeAccessImpl implements Expression {
 
     private List<JsonTableColumn> jsonColumns = new ArrayList<>();
 
-
     public StringBuilder append(StringBuilder builder) {
         builder.append("JSON_TABLE(");
         builder.append(expression.toString());
@@ -28,8 +30,9 @@ public StringBuilder append(StringBuilder builder) {
         }
 
         if (pathExpression != null) {
-            builder.append(", ");
+            builder.append(", '");
             builder.append(pathExpression);
+            builder.append("'");
         }
 
         if (onErrorType != null) {
@@ -57,12 +60,10 @@ public StringBuilder append(StringBuilder builder) {
         }
 
         builder.append("))");
-        return builder;
-    }
 
-    @Override
-    public <T, S> T accept(ExpressionVisitor<T> expressionVisitor, S context) {
-        return expressionVisitor.visit(this, context);
+        super.appendTo(builder, getAlias(), getSampleClause(), getPivot(), getUnPivot());
+
+        return builder;
     }
 
     public void setExpression(Expression expression) {
@@ -105,9 +106,25 @@ public JsonTable withFormatJson(boolean isFormatJson) {
     }
 
     public void setOnErrorType(JsonOnErrorType onErrorType) {
+        if (onErrorType != null) {
+            switch (onErrorType) {
+                case NULL:
+                case ERROR:
+                    break;
+                default:
+                    throw new IllegalArgumentException(
+                            "OnError type " + onErrorType + " is not allowed in JSON_TABLE");
+            }
+        }
+
         this.onErrorType = onErrorType;
     }
 
+    /**
+     * Returns the ON ERROR clause or NULL if none is set
+     *
+     * @return JsonOnErrorType or NULL
+     */
     public JsonOnErrorType getOnErrorType() {
         return onErrorType;
     }
@@ -131,9 +148,24 @@ public JsonTable withType(JsonTableType type) {
     }
 
     public void setOnEmptyType(JsonOnEmptyType onEmptyType) {
+        if (onEmptyType != null) {
+            switch (onEmptyType) {
+                case NULL:
+                case ERROR:
+                    break;
+                default:
+                    throw new IllegalArgumentException(
+                            "OnEmpty type " + onEmptyType + " is not allowed in JSON_TABLE");
+            }
+        }
         this.onEmptyType = onEmptyType;
     }
 
+    /**
+     * Returns the ON EMPTY clause or NULL if none is set
+     *
+     * @return JsonOnEmptyType or NULL
+     */
     public JsonOnEmptyType getOnEmptyType() {
         return onEmptyType;
     }
@@ -147,8 +179,22 @@ public void addColumn(JsonTableColumn column) {
         this.jsonColumns.add(column);
     }
 
+    public JsonTable withColumn(JsonTableColumn column) {
+        addColumn(column);
+        return this;
+    }
+
     public List<JsonTableColumn> getColumns() {
         return jsonColumns;
     }
 
+    @Override
+    public String toString() {
+        return append(new StringBuilder()).toString();
+    }
+
+    @Override
+    public <T, S> T accept(FromItemVisitor<T> fromItemVisitor, S context) {
+        return fromItemVisitor.visit(this, context);
+    }
 }