Damn, I feel like I am so close I now have Postgres working for "plain" JSON_OBJECT() https://github.com/apache/calcite/pull/2733
But it seems to fail when trying to return/serialize the results of queries that have sub-queries using both JSON_ARRAYAGG and JSON_OBJECT Would anyone be willing to give any pointers if they have a clue? Would be greatly appreciated. ===================================================================== Caused by: java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be cast to class java.lang.String at Baz$3.apply(ANONYMOUS.java:95) at Baz$3.apply(ANONYMOUS.java) at org.apache.calcite.adapter.enumerable.BasicAggregateLambdaFactory$AccumulatorAdderSeq.apply(BasicAggregateLambdaFactory.java:81) at org.apache.calcite.linq4j.EnumerableDefaults.aggregate(EnumerableDefaults.java:133) at org.apache.calcite.linq4j.DefaultEnumerable.aggregate(DefaultEnumerable.java:107) at Baz.bind(Baz.java:112) at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363) at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:323) at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578) at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569) at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184) On Fri, Feb 25, 2022 at 3:43 PM Gavin Ray <[email protected]> wrote: > Okay, I have a basic test passing: > > @Test void testJsonObjectPostgres() { > String query = "select json_object(\"product_name\": \"product_id\") > from \"product\""; > final String expected = "SELECT " > + "JSON_BUILD_OBJECT('product_name', > \"product_id\")\n" > + "FROM \"foodmart\".\"product\""; > sql(query).withPostgresql().ok(expected); > } > > I did it by adding the following to "PostgresSqlDialect.java" > in the "unparseCall()" method. Not sure how "proper" this is: > > case OTHER_FUNCTION: > SqlOperator operator = call.getOperator(); > if (operator instanceof SqlJsonObjectFunction) { > assert call.operandCount() % 2 == 1; > final SqlWriter.Frame frame = writer.startFunCall("JSON_BUILD_OBJECT"); > SqlWriter.Frame listFrame = writer.startList("", ""); > for (int i = 1; i < call.operandCount(); i += 2) { > writer.literal("'" + call.operand(i).toString() + "'"); > writer.sep(",", true); > call.operand(i + 1).unparse(writer, leftPrec, rightPrec); > } > writer.endList(listFrame); > writer.endFunCall(frame); > } > break; > > On Fri, Feb 25, 2022 at 2:33 PM Gavin Ray <[email protected]> wrote: > >> It looks like this can be done through: >> >> "SqlDialect#unparseCall(SqlWriter writer, SqlCall call, int leftPrec, int >> rightPrec)" >> >> Hopefully I am on the right track here :sweat_smile: >> >
