SQL Select-Item Aggregation in JSONified Expression

[TearsOfJupiter]

Given the following SQL select item group_concat(distinct myTable.Name) as Name, snippet from a Select query, it would be nice if the JSON generated by, for example, Jackson/FasterXML's ObjectMapper, would include the group_concat aggregation function as a property.

Currently, the SQL is being parsed as a SelectExpressionItem with the expression being a MySQLGroupConcat, but when JSONified, the information about which aggregation function was used is lost. Am I missing something?

[manticore-projects]

Greeetings.

please always provide a concrete sql sample.

[TearsOfJupiter]

Apologies, I'll provide some example SQL:
select group_concat(distinct myTable.Name) as Name from myTable where myTable.Name like '%John%'

[manticore-projects]

Greetings.

Thank you for the example, unfortunately I am not sure what you are looking for. Below is an illustration of the AST. I can see all relevant Nodes?

Are you referring to the Serialization itself? In this case, checkout the pending PR #1624 where I have implemented Serializable for exactly this reason.

[TearsOfJupiter]

Hi, sorry for being unclear. I'm wanting to see the MySQL aggregation function explicitly specified in the JSON. Given the example query from above, I can parse it using JSqlParser into a Select object and then JSONify it using fasterxml:

String sql = "select group_concat(distinct myTable.Name) as Name\n" + "from myTable\n" + "where myTable.Name like '%John%'";
Select select = (Select) CCJSqlParserUtil.parse(sql);
String json = new ObjectMapper().writeValueAsString(select);

This gives me the following (minified) JSON:

{"selectBody":{"distinct":null,"selectItems":[{"expression":{"expressionList":{"expressions":[{"table":{"alias":null,"pivot":null,"sqlServerHints":null,"name":"myTable","unPivot":null,"schemaName":null,"fullyQualifiedName":"myTable","database":{"server":null,"databaseName":null,"fullyQualifiedName":""},"indexHint":null,"astnode":null},"columnName":"Name","fullyQualifiedName":"myTable.Name","astnode":{"id":4}}],"usingBrackets":true},"distinct":true,"orderByElements":null,"separator":null,"astnode":null},"alias":{"name":"Name","useAs":true,"aliasColumns":null},"astnode":{"id":9}}],"intoTables":null,"fromItem":{"alias":null,"pivot":null,"sqlServerHints":null,"name":"myTable","unPivot":null,"schemaName":null,"fullyQualifiedName":"myTable","database":{"server":null,"databaseName":null,"fullyQualifiedName":""},"indexHint":null,"astnode":{"id":5}},"joins":null,"where":{"leftExpression":{"table":{"alias":null,"pivot":null,"sqlServerHints":null,"name":"myTable","unPivot":null,"schemaName":null,"fullyQualifiedName":"myTable","database":{"server":null,"databaseName":null,"fullyQualifiedName":""},"indexHint":null,"astnode":null},"columnName":"Name","fullyQualifiedName":"myTable.Name","astnode":{"id":4}},"rightExpression":{"value":"%John%","prefix":null,"notExcapedValue":"%John%","astnode":{"id":20}},"not":false,"caseInsensitive":false,"escape":null,"stringExpression":"LIKE","astnode":{"id":16}},"groupBy":null,"orderByElements":null,"having":null,"limit":null,"offset":null,"fetch":null,"optimizeFor":null,"skip":null,"mySqlHintStraightJoin":false,"first":null,"top":null,"oracleHierarchical":null,"oracleHint":null,"oracleSiblings":false,"forUpdate":false,"forUpdateTable":null,"useBrackets":false,"wait":null,"mySqlSqlCalcFoundRows":false,"forXmlPath":null,"ksqlWindow":null,"noWait":false,"emitChanges":false,"withIsolation":null,"mySqlSqlCacheFlag":null,"astnode":{"id":6}},"withItemsList":null,"usingWithBrackets":false}

What I would really like to see is something like the following:

{"selectBody":{"distinct":null,"selectItems":[{"expression":{"expressionList":{"expressions":[{"table":{"alias":null,"pivot":null,"sqlServerHints":null,"name":"myTable","unPivot":null,"schemaName":null,"fullyQualifiedName":"myTable","database":{"server":null,"databaseName":null,"fullyQualifiedName":""},"indexHint":null,"astnode":null},"columnName":"Name","fullyQualifiedName":"myTable.Name","astnode":{"id":4}}],"usingBrackets":true},"aggregation":"group_concat","distinct":true,"orderByElements":null,"separator":null,"astnode":null},"alias":{"name":"Name","useAs":true,"aliasColumns":null},"astnode":{"id":9}}],"intoTables":null,"fromItem":{"alias":null,"pivot":null,"sqlServerHints":null,"name":"myTable","unPivot":null,"schemaName":null,"fullyQualifiedName":"myTable","database":{"server":null,"databaseName":null,"fullyQualifiedName":""},"indexHint":null,"astnode":{"id":5}},"joins":null,"where":{"leftExpression":{"table":{"alias":null,"pivot":null,"sqlServerHints":null,"name":"myTable","unPivot":null,"schemaName":null,"fullyQualifiedName":"myTable","database":{"server":null,"databaseName":null,"fullyQualifiedName":""},"indexHint":null,"astnode":null},"columnName":"Name","fullyQualifiedName":"myTable.Name","astnode":{"id":4}},"rightExpression":{"value":"%John%","prefix":null,"notExcapedValue":"%John%","astnode":{"id":20}},"not":false,"caseInsensitive":false,"escape":null,"stringExpression":"LIKE","astnode":{"id":16}},"groupBy":null,"orderByElements":null,"having":null,"limit":null,"offset":null,"fetch":null,"optimizeFor":null,"skip":null,"mySqlHintStraightJoin":false,"first":null,"top":null,"oracleHierarchical":null,"oracleHint":null,"oracleSiblings":false,"forUpdate":false,"forUpdateTable":null,"useBrackets":false,"wait":null,"mySqlSqlCalcFoundRows":false,"forXmlPath":null,"ksqlWindow":null,"noWait":false,"emitChanges":false,"withIsolation":null,"mySqlSqlCacheFlag":null,"astnode":{"id":6}},"withItemsList":null,"usingWithBrackets":false}

Notice the "aggregation": "group_concat", property in the SelectItem's Expression.

My instinct in how to achieve this would be to add a class member to MySQLGroupConcat like:
private final String aggregation = "group_concat"
...
public String getAggregation() { return aggregation; }

This way, ObjectMapper can pick up the class member as part of its JSONification. Function class would probably need the same treatment.

If the PR that you linked to will achieve the same thing, I would be interested in knowing if there's an expected timeframe for when it will get merged. Either way, thank you for taking the time to review my idea!

[manticore-projects]

Yes, I understand that very well because I have had to solve exactly the same challenge for printing an useful AST (as shown in the screenshot). Truth is, JSQLParser has not been developed with Serialization of the AST in mind.

You will need to write your own Jackson Serializer and Deserializer for a few of the Objects, telling which attributes and properties to consider.

We can work on this together since I have started something like that in the Sphinx branch.
The relevant code has a few IF ... THEN ... statements to handle special situations. Although I will not work with Jackson since I do not want to pull that dependency.

For all Leafs in the AST, you will need to serialize all Private Fields.
For some Branches in the AST, you will need to serialize some Private Fields (and that decision would be case by case unfortunately).
For some Branches in the AST, you will not want to serialize the Nodes (Example: Table Name and Alias).

[TearsOfJupiter]

I would be interested in potentially collaborating/contributing, like you mentioned. I admit, however, to only having 7 years' professional software development experience, and relative inexperience with serialization/deserialization, but if I can help, I'd like to try.

[manticore-projects]

I admit, however, to only having 7 years' professional software development experience

No worries, I am just the janitor here and will learn a lot from you.
Can you open a branch/repo where I can see what has been done on the serialization so far?

My own serialization is here: https://github.com/manticore-projects/jsqlformatter/blob/e3bc912f3257f91ff5c880567ca0718ef4a6887a/src/main/java/com/manticore/jsqlformatter/JSQLFormatter.java#L1152
Although you will want some explanations what this stuff is doing.

[TearsOfJupiter]

Gonna have to postpone the idea of contributing for a while. Work looks like it's going to be crazy busy for the near future.