Investigate free form queries against the metadata column

[machristie]

The Calcite integration only support queries against pre-registered metadata schema fields, like so

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:

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:

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.