You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Gavin Ray <ra...@gmail.com> on 2022/02/25 02:21:27 UTC
I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated
As title implies, as part of a larger effort to fix a couple of things
related to JSON support,
I'd really like to add support for converting JSON_OBJECT and JSON_ARRAYAGG
calls to the right DB-specific syntax:
https://issues.apache.org/jira/browse/CALCITE-4739
I thought that maybe this functionality would be something that comes out
of "SqlDialect",
or "RelDataTypeSystem", but this doesn't seem to be the case.
Does anyone happen to know what the process would look like roughly to add
this?
Thank you =)
---------------------------
As an aside, after doing some research, it seems nearly every dialect falls
into one of two major categories for JSON syntax.
With SQL-Server having it's own thing because, hey screw us right?
So it shouldn't be super bad I think
-- Postgres & Co (Cockroach, Yugabyte)
SELECT
"id",
"name",
(
SELECT json_agg(json_build_object(
'x', CAST(1 AS bigint),
'foo', CAST('bar' AS varchar)
))
)
FROM "user"
GROUP BY "id", "name"
-- Oracle, MySQL, basically everything that isn't Postgres/SQL-Server
SELECT
"id",
"name",
(
SELECT JSON_ARRAYAGG(JSON_OBJECT(
KEY 'x' VALUE 1,
KEY 'foo' VALUE 'bar'
))
)
FROM "user"
GROUP BY "id", "name";
Re: I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated
Posted by Gavin Ray <ra...@gmail.com>.
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 <ra...@gmail.com> 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 <ra...@gmail.com> 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:
>>
>
Re: I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated
Posted by Gavin Ray <ra...@gmail.com>.
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 <ra...@gmail.com> 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:
>
Re: I would like to fix "CALCITE-4739: json_object pushdown JDBC". Looking through the codebase, not obvious where to start, hints greatly appreciated
Posted by Gavin Ray <ra...@gmail.com>.
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: