Improve JSON Functions

Bring back Postgres Syntax Enable MySQL Syntax JSON_OBJECT(key, value [, key, value, ...]) Fix some more tests, where key was not a String
JSQLParser · wumpz · Apr 9, 2022 · Nov 22, 2021 · Nov 24, 2021 · Nov 28, 2021
commit 02cac90800c8822976554da8bf15047288ade3bb
diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonFunction.java b/src/main/java/net/sf/jsqlparser/expression/JsonFunction.java
@@ -57,6 +57,10 @@ public void add(int i, JsonFunctionExpression expression) {
     expressions.add(i, expression);
   }
 
+  public boolean isEmpty() {
+    return keyValuePairs.isEmpty();
+  }
+
   public JsonAggregateOnNullType getOnNullType() {
     return onNullType;
   }
@@ -122,6 +126,9 @@ public StringBuilder append(StringBuilder builder) {
       case POSTGRES_OBJECT:
         appendPostgresObject(builder);
         break;
+      case MYSQL_OBJECT:
+        appendMySqlObject(builder);
+        break;
       case ARRAY:
         appendArray(builder);
         break;
@@ -200,6 +207,22 @@ public StringBuilder appendPostgresObject(StringBuilder builder) {
     return builder;
   }
 
+  public StringBuilder appendMySqlObject(StringBuilder builder) {
+    builder.append("JSON_OBJECT( ");
+    int i=0;
+    for (JsonKeyValuePair keyValuePair : keyValuePairs) {
+      if (i>0) {
+        builder.append(", ");
+      }
+      builder.append(keyValuePair.getKey());
+      builder.append(", ").append(keyValuePair.getValue());
+      i++;
+    }
+    builder.append(" ) ");
+
+    return builder;
+  }
+
   @SuppressWarnings({"PMD.CyclomaticComplexity", "PMD.NPathComplexity"})
   public StringBuilder appendArray(StringBuilder builder) {
     builder.append("JSON_ARRAY( ");

diff --git a/src/main/java/net/sf/jsqlparser/expression/JsonFunctionType.java b/src/main/java/net/sf/jsqlparser/expression/JsonFunctionType.java
@@ -18,4 +18,5 @@ public enum JsonFunctionType {
     OBJECT
     , ARRAY
     , POSTGRES_OBJECT
+    , MYSQL_OBJECT
 }
diff --git a/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt b/src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
@@ -3729,6 +3729,8 @@ Expression PrimaryExpression() #PrimaryExpression:
 
         | LOOKAHEAD(JsonExpression()) retval=JsonExpression()
 
+        | LOOKAHEAD(Function()) retval=Function() [ LOOKAHEAD(2) retval = AnalyticExpression( (Function) retval ) ]
+
         | LOOKAHEAD(JsonFunction()) retval = JsonFunction()
 
         | LOOKAHEAD(JsonAggregateFunction()) retval = JsonAggregateFunction()
@@ -3737,7 +3739,7 @@ Expression PrimaryExpression() #PrimaryExpression:
 
         | LOOKAHEAD(FullTextSearch()) retval = FullTextSearch()
 
-        | LOOKAHEAD(Function()) retval=Function() [ LOOKAHEAD(2) retval = AnalyticExpression( (Function) retval ) ]
+
 
         | LOOKAHEAD(2) retval = IntervalExpression() { dateExpressionAllowed = false; }
 
@@ -3975,6 +3977,7 @@ JsonFunction JsonFunction() : {
     JsonFunction result = new JsonFunction();
     boolean usingKeyKeyword = false;
     boolean usingValueKeyword = false;
+    boolean usingFormatJason = false;
     Token keyToken;
     Token valueToken = null;
     Column column = null;
@@ -3989,73 +3992,72 @@ JsonFunction JsonFunction() : {
         ( 
           ( <K_JSON_OBJECT> 
             "(" { result.setType( JsonFunctionType.OBJECT ); }
-//                LOOKAHEAD(2)
-//                (
-//                    // Postgres Specific Syntax:
-//                    // SELECT json_object('{a, 1, b, 2}');
-//                    // SELECT json_object('{{a, 1}, {b, 2}}');
-//                    // SELECT json_object('{a, b}', '{1,2 }');
-//                    { result.setType( JsonFunctionType.POSTGRES_OBJECT ); }
-//                    keyToken = <S_CHAR_LITERAL>
-//                    [ "," valueToken = <S_CHAR_LITERAL> ]
-//                    { keyValuePair = new JsonKeyValuePair( keyToken.image, valueToken !=null ? valueToken.image : null, false, false ); result.add(keyValuePair); }
-//                )
-//                |
-
                 (
-                    // --- First Element
-                    // SQL2016 compliant Syntax
-                    [   "KEY" { usingKeyKeyword = true; } ]
-                    keyToken = <S_CHAR_LITERAL>
-
                     (
-                        ( ":" | "VALUE" { usingValueKeyword = true; } )
+                        // SQL2016 compliant Syntax
                         (
-                            column = Column() { keyValuePair = new JsonKeyValuePair( keyToken.image, column, usingKeyKeyword, usingValueKeyword ); result.add(keyValuePair); }
-                            |
-                            ( valueToken = <DT_ZONE> | valueToken = <S_DOUBLE> | valueToken = <S_LONG> | valueToken = <S_HEX> | valueToken = <S_CHAR_LITERAL> ) { keyValuePair = new JsonKeyValuePair( keyToken.image, valueToken.image, usingKeyKeyword, usingValueKeyword ); result.add(keyValuePair); }
-                        )
-                        [ <K_FORMAT> <K_JSON> { keyValuePair.setUsingFormatJson( true ); } ]
-
-                        // --- Next Elements
-                        (   ","  { usingKeyKeyword = false; usingValueKeyword = false;  }
                             [   "KEY" { usingKeyKeyword = true; } ]
                             keyToken = <S_CHAR_LITERAL>
-                            ( ":" | "VALUE" { usingValueKeyword = true; } )
-                            (
-                                column = Column() { keyValuePair = new JsonKeyValuePair( keyToken.image, column, usingKeyKeyword, usingValueKeyword ); result.add(keyValuePair); }
-                                |
-                                ( valueToken = <DT_ZONE> | valueToken = <S_DOUBLE> | valueToken = <S_LONG> | valueToken = <S_HEX> | valueToken = <S_CHAR_LITERAL> ) { keyValuePair = new JsonKeyValuePair( keyToken.image, valueToken.image, usingKeyKeyword, usingValueKeyword ); result.add(keyValuePair); }
-                            )
-                            [ <K_FORMAT> <K_JSON> { keyValuePair.setUsingFormatJson( true ); } ]
-                        )*
-                    )
-//                    |
-//                    (
-//                        [ "," valueToken = <S_CHAR_LITERAL> ]
-//                        { keyValuePair = new JsonKeyValuePair( keyToken.image, valueToken !=null ? valueToken.image : null, false, false ); result.add(keyValuePair); }
-//                    )
-                )?
-
-                [
-                  (
-                      <K_NULL> <K_ON> <K_NULL>  { result.setOnNullType(  JsonAggregateOnNullType.NULL ); }
-                  )
-                  |
-                  (
-                      <K_ABSENT> <K_ON> <K_NULL> { result.setOnNullType(  JsonAggregateOnNullType.ABSENT ); }
-                  )
-                ]
 
-                [
-                  (
-                      <K_WITH> <K_UNIQUE> <K_KEYS> { result.setUniqueKeysType( JsonAggregateUniqueKeysType.WITH ); }
-                  )
-                  |
-                  (
-                      <K_WITHOUT> <K_UNIQUE> <K_KEYS> { result.setUniqueKeysType( JsonAggregateUniqueKeysType.WITHOUT ); }
-                  )
-                ]
+                             ( LOOKAHEAD(2)
+                                ( ":" |  "," { result.setType( JsonFunctionType.POSTGRES_OBJECT ); } | "VALUE" { usingValueKeyword = true; } )
+                                (
+                                    column = Column()
+                                    |
+                                    ( valueToken = <DT_ZONE> | valueToken = <S_DOUBLE> | valueToken = <S_LONG> | valueToken = <S_HEX> | valueToken = <S_CHAR_LITERAL> )
+                                )
+                                [ <K_FORMAT> <K_JSON> { usingFormatJason = true; } ]
+                            )? {
+                                   if (valueToken !=null) {
+                                       keyValuePair = new JsonKeyValuePair( keyToken.image, valueToken.image, usingKeyKeyword, usingValueKeyword );
+                                       keyValuePair.setUsingFormatJson( usingFormatJason );
+                                       result.add(keyValuePair);
+                                   } else if (column !=null) {
+                                       keyValuePair = new JsonKeyValuePair( keyToken.image, column, usingKeyKeyword, usingValueKeyword );
+                                       keyValuePair.setUsingFormatJson( usingFormatJason );
+                                       result.add(keyValuePair);
+                                  } else {
+                                       result.setType( JsonFunctionType.POSTGRES_OBJECT );
+                                       keyValuePair = new JsonKeyValuePair( keyToken.image, null, false, false );
+                                       result.add(keyValuePair);
+                                  }
+                               }
+
+                            // --- Next Elements
+                            (   ","  { usingKeyKeyword = false; usingValueKeyword = false;  }
+                                [   "KEY" { usingKeyKeyword = true; } ]
+                                keyToken = <S_CHAR_LITERAL>
+                                ( ":" | ","  { result.setType( JsonFunctionType.MYSQL_OBJECT ); } | "VALUE" { usingValueKeyword = true; } )
+                                (
+                                    column = Column() { keyValuePair = new JsonKeyValuePair( keyToken.image, column, usingKeyKeyword, usingValueKeyword ); result.add(keyValuePair); }
+                                    |
+                                    ( valueToken = <DT_ZONE> | valueToken = <S_DOUBLE> | valueToken = <S_LONG> | valueToken = <S_HEX> | valueToken = <S_CHAR_LITERAL> ) { keyValuePair = new JsonKeyValuePair( keyToken.image, valueToken.image, usingKeyKeyword, usingValueKeyword ); result.add(keyValuePair); }
+                                )
+                                [ <K_FORMAT> <K_JSON> { keyValuePair.setUsingFormatJson( true ); } ]
+                            )*
+                        )
+                    )?
+
+                    [
+                      (
+                          <K_NULL> <K_ON> <K_NULL>  { result.setOnNullType(  JsonAggregateOnNullType.NULL ); }
+                      )
+                      |
+                      (
+                          <K_ABSENT> <K_ON> <K_NULL> { result.setOnNullType(  JsonAggregateOnNullType.ABSENT ); }
+                      )
+                    ]
+
+                    [
+                      (
+                          <K_WITH> <K_UNIQUE> <K_KEYS> { result.setUniqueKeysType( JsonAggregateUniqueKeysType.WITH ); }
+                      )
+                      |
+                      (
+                          <K_WITHOUT> <K_UNIQUE> <K_KEYS> { result.setUniqueKeysType( JsonAggregateUniqueKeysType.WITHOUT ); }
+                      )
+                    ]
+                )
             ")"
           )
           |

diff --git a/src/test/java/net/sf/jsqlparser/expression/ExpressionVisitorAdapterTest.java b/src/test/java/net/sf/jsqlparser/expression/ExpressionVisitorAdapterTest.java
@@ -216,7 +216,7 @@ public void testAtTimeZoneExpression() throws JSQLParserException {
     public void testJsonFunction() throws JSQLParserException {
         ExpressionVisitorAdapter adapter = new ExpressionVisitorAdapter();
         CCJSqlParserUtil
-                .parseExpression("JSON_OBJECT( KEY foo VALUE bar, KEY foo VALUE bar)")
+                .parseExpression("JSON_OBJECT( KEY 'foo' VALUE bar, KEY 'foo' VALUE bar)")
                 .accept(adapter);
         CCJSqlParserUtil
                 .parseExpression("JSON_ARRAY( (SELECT * from dual) )")

diff --git a/src/test/java/net/sf/jsqlparser/expression/JsonFunctionTest.java b/src/test/java/net/sf/jsqlparser/expression/JsonFunctionTest.java
@@ -162,10 +162,13 @@ public void testObject() throws JSQLParserException {
     public void testObjectIssue1504() throws JSQLParserException {
         TestUtils.assertSqlCanBeParsedAndDeparsed(
                 "SELECT JSON_OBJECT(key 'person' value tp.account) obj", true);
+
         TestUtils.assertSqlCanBeParsedAndDeparsed(
                 "SELECT JSON_OBJECT(key 'person' value tp.account, key 'person' value tp.account) obj", true);
+
         TestUtils.assertSqlCanBeParsedAndDeparsed(
                 "SELECT JSON_OBJECT( 'person' : tp.account) obj", true);
+
         TestUtils.assertSqlCanBeParsedAndDeparsed(
                 "SELECT JSON_OBJECT( 'person' : tp.account, 'person' : tp.account) obj", true);
 
@@ -174,7 +177,12 @@ public void testObjectIssue1504() throws JSQLParserException {
 
         TestUtils.assertSqlCanBeParsedAndDeparsed(
                 "SELECT JSON_OBJECT( 'person' VALUE tp.person, 'account' VALUE tp.account) obj", true);
+    }
 
+    @Test
+    public void testObjectMySQL() throws JSQLParserException {
+        TestUtils.assertSqlCanBeParsedAndDeparsed(
+                "SELECT JSON_OBJECT('person', tp.person, 'account', tp.account) obj", true);
     }
 
     @Test
@@ -225,9 +233,9 @@ public void testIssue1260() throws JSQLParserException {
 
     @Test
     public void testIssue1371() throws JSQLParserException {
-//        TestUtils.assertSqlCanBeParsedAndDeparsed("SELECT json_object('{a, 1, b, 2}')", true);
-//        TestUtils.assertSqlCanBeParsedAndDeparsed("SELECT json_object('{{a, 1}, {b, 2}}')", true);
-//        TestUtils.assertSqlCanBeParsedAndDeparsed("SELECT json_object('{a, b}', '{1,2 }')", true);
+        TestUtils.assertSqlCanBeParsedAndDeparsed("SELECT json_object('{a, 1, b, 2}')", true);
+        TestUtils.assertSqlCanBeParsedAndDeparsed("SELECT json_object('{{a, 1}, {b, 2}}')", true);
+        TestUtils.assertSqlCanBeParsedAndDeparsed("SELECT json_object('{a, b}', '{1,2 }')", true);
     }
 
     @Test

diff --git a/src/test/java/net/sf/jsqlparser/util/validation/validator/ExpressionValidatorTest.java b/src/test/java/net/sf/jsqlparser/util/validation/validator/ExpressionValidatorTest.java
@@ -226,7 +226,7 @@ public void testJsonFunctionExpression() throws JSQLParserException {
     public void testJsonAggregartFunctionExpression() throws JSQLParserException {
         validateNoErrors("SELECT JSON_ARRAYAGG( a FORMAT JSON ABSENT ON NULL ) FILTER( WHERE name = 'Raj' ) OVER( PARTITION BY name ) FROM mytbl", 1,
                 EXPRESSIONS);
-        validateNoErrors("SELECT JSON_OBJECT( KEY foo VALUE bar FORMAT JSON, foo:bar, foo:bar ABSENT ON NULL) FROM mytbl", 1,
+        validateNoErrors("SELECT JSON_OBJECT( KEY 'foo' VALUE bar FORMAT JSON, 'foo':bar, 'foo':bar ABSENT ON NULL) FROM mytbl", 1,
                 EXPRESSIONS);
     }
-Original file line number
+Diff line change
@@ Expand Up / @@ -18,4 +18,5 @@ public enum JsonFunctionType { @@
         OBJECT
         , ARRAY
         , POSTGRES_OBJECT
+        , MYSQL_OBJECT
     }