I have been working on generating a SQL query using Calcite. Based on some earlier guidance I started using relational algebra to build the tree and looking to use JSON functions since some of the predicates are on data in a column with JSON.
In order to apply the predicates on JSON data I figured I should create a custom schema which can be used within the RelBuilder to provide metadata about the JSON column and subsequently the types of the nested fields in the JSON (based on the json schema for the data within). The example I am looking at to build the custom schema + table is the CSVTable example... looking at the getRowType overridden function which creates field information for the CSV data. My approach is to provide the information on the column and the nested elements of the JSON via a custom schema + table implementation. A couple questions (a) I do not see a JSON data type - what should I use? (b) I was thinking of applying the JSON functions based on the metadata from the custom schema which identifies the JSON field(s). Is this the right approach? Is there something I should change? Thanks in advance, -Sandeep
