Thanks Stamatis for the examples and links.
On Fri, Nov 26, 2021 at 6:05 AM Stamatis Zampetakis <[email protected]> wrote: > 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 > > > > > > > > > >
