[GitHub] [airavata-data-catalog] machristie opened a new issue, #27: Investigate free form queries against the metadata column

["machristie (via GitHub)" <gi...@apache.org>]

machristie opened a new issue, #27:
URL: https://github.com/apache/airavata-data-catalog/issues/27

   The Calcite integration only support queries against pre-registered metadata schema fields, like so
   ```sql
   SELECT
       *
   FROM
       my_schema
   WHERE
       (
           field1 < 5
           OR field3 = 'bar'
       )
       AND field1 > 0
       AND external_id = 'fff';
   ```
   
   But it would be nice if one could query against unregistered metadata schema fields that are known to exist within the metadata JSONB column, something like:
   ```sql
   SELECT
       *
   FROM
       my_schema
   WHERE
       metadata.some_other_field > 0;
   ```
   
   There are two challenges. One is how to relax Calcite's validation to allow referencing fields that aren't known ahead of time. Second is how to support a syntax for referencing a JSON field that Calcite will parse.
   
   One option might be to have the client queries use JSON functions that Calcite supports: https://calcite.apache.org/docs/reference.html#json-functions
   
   For example:
   ```sql
   SELECT
       *
   FROM
       my_schema
   WHERE
       JSON_EXISTS(metadata, '$.some_other_field > 0');
   ```
   
   But, PostgreSQL doesn't yet natively support these functions (see https://www.depesz.com/2022/04/01/waiting-for-postgresql-15-sql-json-query-functions/) so they would need to be rewritten. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@airavata.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org