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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
