Skip to content

Commit

Permalink
fix: JSon functions with Column parameters
Browse files Browse the repository at this point in the history 
- fixes #1753

Signed-off-by: Andreas Reichel <andreas@manticore-projects.com>
  • Loading branch information
@manticore-projects
manticore-projects committed Nov 29, 2024
1 parent 7ed3ce9 commit 8225178
Show file tree
Hide file tree
Showing 5 changed files with 138 additions and 91 deletions.
6 changes: 3 additions & 3 deletions build.gradle
View file
Original file line number Diff line number Diff line change
Expand Up @@ -95,9 +95,9 @@ dependencies {
testImplementation 'com.h2database:h2:+'

// for JaCoCo Reports
testImplementation 'org.junit.jupiter:junit-jupiter-api:+'
testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:+'
testImplementation 'org.junit.jupiter:junit-jupiter-params:+'
testImplementation 'org.junit.jupiter:junit-jupiter-api:5.11.3'
testRuntimeOnly 'org.junit.jupiter:junit-jupiter-engine:5.11.3'
testImplementation 'org.junit.jupiter:junit-jupiter-params:5.11.3'

// https://mvnrepository.com/artifact/org.mockito/mockito-junit-jupiter
testImplementation 'org.mockito:mockito-junit-jupiter:+'
Expand Down
11 changes: 7 additions & 4 deletions src/main/java/net/sf/jsqlparser/expression/JsonAggregateFunction.java
View file
Original file line number Diff line number Diff line change
Expand Up @@ -23,7 +23,7 @@ public class JsonAggregateFunction extends FilterOverImpl implements Expression
private JsonFunctionType functionType;
private Expression expression = null;
private boolean usingKeyKeyword = false;
private String key;
private Object key;
private boolean usingValueKeyword = false;
private Object value;

Expand Down Expand Up @@ -112,15 +112,15 @@ public JsonAggregateFunction withUsingKeyKeyword(boolean usingKeyKeyword) {
return this;
}

public String getKey() {
public Object getKey() {
return key;
}

public void setKey(String key) {
public void setKey(Object key) {
this.key = key;
}

public JsonAggregateFunction withKey(String key) {
public JsonAggregateFunction withKey(Object key) {
this.setKey(key);
return this;
}
Expand Down Expand Up @@ -188,6 +188,7 @@ public <T, S> T accept(ExpressionVisitor<T> expressionVisitor, S context) {
public StringBuilder append(StringBuilder builder) {
switch (functionType) {
case OBJECT:
case MYSQL_OBJECT:
appendObject(builder);
break;
case ARRAY:
Expand All @@ -209,6 +210,8 @@ public StringBuilder appendObject(StringBuilder builder) {
builder.append("KEY ");
}
builder.append(key).append(" VALUE ").append(value);
} else if (functionType == JsonFunctionType.MYSQL_OBJECT) {
builder.append(key).append(", ").append(value);
} else {
builder.append(key).append(":").append(value);
}
Expand Down
6 changes: 3 additions & 3 deletions src/main/java/net/sf/jsqlparser/expression/JsonKeyValuePair.java
View file
Original file line number Diff line number Diff line change
Expand Up @@ -18,13 +18,13 @@
*/

public class JsonKeyValuePair implements Serializable {
private final String key;
private final Object key;
private final Object value;
private boolean usingKeyKeyword = false;
private boolean usingValueKeyword = false;
private boolean usingFormatJson = false;

public JsonKeyValuePair(String key, Object value, boolean usingKeyKeyword,
public JsonKeyValuePair(Object key, Object value, boolean usingKeyKeyword,
boolean usingValueKeyword) {
this.key = Objects.requireNonNull(key, "The KEY of the Pair must not be null");
this.value = value;
Expand Down Expand Up @@ -93,7 +93,7 @@ public boolean equals(Object obj) {
return Objects.equals(this.key, other.key);
}

public String getKey() {
public Object getKey() {
return key;
}

Expand Down
193 changes: 112 additions & 81 deletions src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
View file
Original file line number Diff line number Diff line change
Expand Up @@ -5193,101 +5193,109 @@ JsonFunction JsonFunction() : {
Column column = null;
JsonKeyValuePair keyValuePair;

Object key = null;
Expression expression = null;
JsonFunctionExpression functionExpression;

}
{
(
(
( <K_JSON_OBJECT>
"(" { result.setType( JsonFunctionType.OBJECT ); }
(
( <K_JSON_OBJECT>
"(" { result.setType( JsonFunctionType.OBJECT ); }
(
// SQL2016 compliant Syntax
LOOKAHEAD(2) (
LOOKAHEAD(2) (
"KEY" { usingKeyKeyword = true; } ( keyToken = <S_CHAR_LITERAL> { key = keyToken.image; } | key = Column() )
)
|
keyToken = <S_CHAR_LITERAL> { key = keyToken.image; }
|
key = Column()
)

( LOOKAHEAD(2)
( ":" | "," { result.setType( JsonFunctionType.POSTGRES_OBJECT ); } | "VALUE" { usingValueKeyword = true; } )
(
// SQL2016 compliant Syntax
(
[ "KEY" { usingKeyKeyword = true; } ]
keyToken = <S_CHAR_LITERAL>
expression = Expression()
)
[ <K_FORMAT> <K_JSON> { usingFormatJason = true; } ]
)?
{
if (expression !=null) {
keyValuePair = new JsonKeyValuePair( key, expression, usingKeyKeyword, usingValueKeyword );
keyValuePair.setUsingFormatJson( usingFormatJason );
result.add(keyValuePair);
} else {
result.setType( JsonFunctionType.POSTGRES_OBJECT );
keyValuePair = new JsonKeyValuePair( key, null, false, false );
result.add(keyValuePair);
}
}

( LOOKAHEAD(2)
( ":" | "," { result.setType( JsonFunctionType.POSTGRES_OBJECT ); } | "VALUE" { usingValueKeyword = true; } )
(
expression = Expression()
)
[ <K_FORMAT> <K_JSON> { usingFormatJason = true; } ]
)? {
if (expression !=null) {
keyValuePair = new JsonKeyValuePair( keyToken.image, expression, 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; } )
(
expression = Expression() { keyValuePair = new JsonKeyValuePair( keyToken.image, expression, usingKeyKeyword, usingValueKeyword ); result.add(keyValuePair); }
)
[ <K_FORMAT> <K_JSON> { keyValuePair.setUsingFormatJson( true ); } ]
)*
// --- Next Elements
( "," { usingKeyKeyword = false; usingValueKeyword = false; }
(
LOOKAHEAD(2) (
"KEY" { usingKeyKeyword = true; } ( keyToken = <S_CHAR_LITERAL> { key = keyToken.image; } | key = Column() )
)
)?
|
keyToken = <S_CHAR_LITERAL> { key = keyToken.image; }
|
key = Column()
)
( ":" | "," { result.setType( JsonFunctionType.MYSQL_OBJECT ); } | "VALUE" { usingValueKeyword = true; } )
expression = Expression() { keyValuePair = new JsonKeyValuePair( key, expression, 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_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 ); }
)
]
[
(
<K_WITH> <K_UNIQUE> <K_KEYS> { result.setUniqueKeysType( JsonAggregateUniqueKeysType.WITH ); }
)
|
(
<K_WITHOUT> <K_UNIQUE> <K_KEYS> { result.setUniqueKeysType( JsonAggregateUniqueKeysType.WITHOUT ); }
)
]
")"
)
|
(
<K_JSON_ARRAY> { result.setType( JsonFunctionType.ARRAY ); }
"("
(
LOOKAHEAD(2) (
<K_NULL> <K_ON> <K_NULL> { result.setOnNullType( JsonAggregateOnNullType.NULL ); }
)
")"
)
|
(
<K_JSON_ARRAY> { result.setType( JsonFunctionType.ARRAY ); }
"("
|
expression=Expression() { functionExpression = new JsonFunctionExpression( expression ); result.add( functionExpression ); }

[ LOOKAHEAD(2) <K_FORMAT> <K_JSON> { functionExpression.setUsingFormatJson( true ); } ]
(
LOOKAHEAD(2) (
<K_NULL> <K_ON> <K_NULL> { result.setOnNullType( JsonAggregateOnNullType.NULL ); }
)
|
","
expression=Expression() { functionExpression = new JsonFunctionExpression( expression ); result.add( functionExpression ); }

[ LOOKAHEAD(2) <K_FORMAT> <K_JSON> { functionExpression.setUsingFormatJson( true ); } ]
(
","
expression=Expression() { functionExpression = new JsonFunctionExpression( expression ); result.add( functionExpression ); }
[ LOOKAHEAD(2) <K_FORMAT> <K_JSON> { functionExpression.setUsingFormatJson( true ); } ]
)*
)*
)*

[
<K_ABSENT> <K_ON> <K_NULL> { result.setOnNullType( JsonAggregateOnNullType.ABSENT ); }
]
[
<K_ABSENT> <K_ON> <K_NULL> { result.setOnNullType( JsonAggregateOnNullType.ABSENT ); }
]

")"
)
)
")"
)
)

{
Expand All @@ -5298,6 +5306,7 @@ JsonFunction JsonFunction() : {
JsonAggregateFunction JsonAggregateFunction() : {
JsonAggregateFunction result = new JsonAggregateFunction();
Token token;
Object key;
Expression expression;
List<OrderByElement> expressionOrderByList = null;

Expand All @@ -5312,10 +5321,32 @@ JsonAggregateFunction JsonAggregateFunction() : {
(
( <K_JSON_OBJECTAGG>
"(" { result.setType( JsonFunctionType.OBJECT ); }
[ "KEY" { result.setUsingKeyKeyword( true ); } ]
( token = <DT_ZONE> | token = <S_DOUBLE> | token = <S_LONG> | token = <S_HEX> | token = <S_CHAR_LITERAL> | token = <S_IDENTIFIER> | token = <S_QUOTED_IDENTIFIER> ) { result.setKey( token.image ); }
( ":" | "VALUE" {result.setUsingValueKeyword( true ); } )
( token = <S_IDENTIFIER> | token = <S_QUOTED_IDENTIFIER> ) { result.setValue( token.image ); }
(
LOOKAHEAD(2) (
"KEY" { result.setUsingKeyKeyword( true ); }
(
( token = <DT_ZONE> | token = <S_DOUBLE> | token = <S_LONG> | token = <S_HEX> | token = <S_CHAR_LITERAL> )
{
key = token.image;
}
|
key = Column()
)
)
|
( token = <DT_ZONE> | token = <S_DOUBLE> | token = <S_LONG> | token = <S_HEX> | token = <S_CHAR_LITERAL> )
{
key = token.image;
}
|
key = Column()
)
{
result.setKey( key );
}

( ":" | "," { result.setType( JsonFunctionType.MYSQL_OBJECT ); } | "VALUE" {result.setUsingValueKeyword( true ); } )
expression = Expression() { result.setValue( expression ); }

[ <K_FORMAT> <K_JSON> { result.setUsingFormatJson( true ); } ]

Expand Down
13 changes: 13 additions & 0 deletions src/test/java/net/sf/jsqlparser/expression/JsonFunctionTest.java
View file
Original file line number Diff line number Diff line change
Expand Up @@ -131,6 +131,10 @@ public void testArrayAgg() throws JSQLParserException {

@Test
public void testObject() throws JSQLParserException {
TestUtils.assertSqlCanBeParsedAndDeparsed(
"WITH Items AS (SELECT 'hello' AS key, 'world' AS value)\n" +
"SELECT JSON_OBJECT(key, value) AS json_data FROM Items",
true);
TestUtils.assertSqlCanBeParsedAndDeparsed(
"SELECT JSON_OBJECT( KEY 'foo' VALUE bar, KEY 'foo' VALUE bar) FROM dual ", true);
TestUtils.assertSqlCanBeParsedAndDeparsed(
Expand Down Expand Up @@ -280,4 +284,13 @@ public void testJavaMethods() throws JSQLParserException {
Assertions.assertEquals(JsonAggregateUniqueKeysType.WITH, jsonFunction
.withUniqueKeysType(JsonAggregateUniqueKeysType.WITH).getUniqueKeysType());
}

@Test
void testIssue1753JSonObjectAggWithColumns() throws JSQLParserException {
String sqlStr = "SELECT JSON_OBJECTAGG( KEY q.foo VALUE q.bar) FROM dual";
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr);

sqlStr = "SELECT JSON_OBJECTAGG(foo, bar) FROM dual";
TestUtils.assertSqlCanBeParsedAndDeparsed(sqlStr);
}
}

0 comments on commit 8225178

Please sign in to comment.