Hi Sandeep, If you have a column holding JSON data then if you want to apply predicates on some field inside the JSON you most likely need to create calls to JSON_EXIST function [1]. If you check SqlToRelConverterTest you will find some examples of how the relational algebra should look like when you use JSON specific functions [2]. To create the plan you will most likely need to use the RelBuilder#call [3] API. Have a look in RelBuilderTest for examples of using the call API.
Best, Stamatis [1] https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java#L1377 [2] https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml#L3406 [3] https://github.com/apache/calcite/blob/7c423ef23878271b1c50c03629ebfff674985681/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L661 On Wed, Nov 24, 2021 at 9:43 PM Sandeep Nayak <[email protected]> wrote: > Thanks all for the pointer to go with the relational algebra approach. I > will work with that route, I did have a follow up question on that path. Is > there support in the RelBuilder to apply predicates on JSON data stored in > a single column in a table? > > I noted tests in RelToSqlConverterTest for queries on json but did not see > one which used the RelBuilder to apply predicates on a field which carries > JSON data. Is there a convention to represent a field inside a json? > Example: table t has column a which has a json inside say { "x" : > "some-string-value", "y": some-integer-value } and say I want to express a > predicate like y > some-ceiling-value. > > -Sandeep > > On Wed, Nov 24, 2021 at 3:00 AM Florent Martineau <[email protected] > > > wrote: > > > Hi Sandeep, > > > > I'm no expert so take what I'm saying with a grain of salt. > > > > I use Calcite to generate queries by using the RelBuilder, which will > > create RelNodes that I can then convert to SQL when I need to execute the > > query. > > > > From my understanding, if we use Relational Algebra it's because it's > much > > easier to manipulate and reason with, than SQL. Therefore it makes more > > sense for me to build the query using relational algebra and then convert > > it back to SQL. > > > > TL;DR: I don't use SQL Nodes to build queries, but RelBuilder then > > RelToSQLConverter. > > > > Hope this helps, > > > > Florent > > > > On Wed, Nov 24, 2021, 11:38 AM Sandeep Nayak < > [email protected]> > > wrote: > > > > > Hello, > > > > > > I have been looking for a library which will allow me to use an object > > tree > > > to generate a SQL. Calcite came up in my search but after taking a look > > at > > > the codebase my conclusion is that Calcite provides the ability to > parse > > > SQL statements into an object tree represented as SQLNode (and derived > > > classes) instances which subsequently can be used to perform > > optimizations > > > by generating relational algebra for the logical query. > > > > > > Calcite however does not provide classes which can be used to assemble > a > > > tree and generate SQL statements. From the contracts at least SQLNode > > takes > > > in a Parser position and there are no other contracts which indicate > such > > > an option. Can someone confirm if this is inaccurate? > > > > > > If not Calcite, are there other open source libraries out there which > > allow > > > this? > > > > > > Thanks in advance. > > > > > > -Sandeep > > > > > >
