No operator for "x is a superset of y" (CQL)

[jisantuc]

The STAC query extension borrows its list of operators from the predicates here. While implementing it, we noticed that there doesn't seem to be an operator to express a subset relationship.

In this conversation, we wanted to query for json with a string array that contained another string array. e.g.

  "layer:ids": {
    "mysteryOperator": ["layers-ca", "layers-us"]
  }

should match objects where layer:ids is a string array containing both layers-ca and layers-us, and otherwise not match.

We fell back to superset to meet our short-term needs. Are we reading correctly that no existing operator serves this purpose?

[cportele]

Yes, this is a known (and so far, intentional) limitation. All comparisons are between scalar values. For cases where property values may be arrays an extension is needed, which has not yet been specified.

[cportele]

Meeting 2020-05-25: @pvretano to take a look at how databases implement predicates on array values in SQL.

[pvretano]

So most databases have support for arrays and also support comparison operators for arrays (although the specific set of operators varies). ISO SQLXXXX, for example, only supports two comparison operators = and <>. Commercial-level databases (e.g. Oracle, Postgesql, etc.) support many more operators and include subset, superset and overlap operators for arrays. So, for example, in postgresql you would say something like: ids @> ARRAY['layers-ca','layers-us'] to perform the evaluation that @jisantuc presents above. The '@>' is the contains operator.
So, can we add array support to CQL? Yup. It's fairly straightforward to add all the syntactic bits to the text and JSON encodings of CQL with a fairly small but useful set of operators. Should we add this support to the core? Not sure ... must discuss in the SWG although to me, this feels more like an extension at the moment.

[cportele]

I agree it should not be in "Simple CQL", but it could still make sense to include it in part 3 since array values / properties with multiplicity greater than one are valid, but currently it is unspecified how to interpret the operators in Simple CQL for such queryables.

One options could be:

• In Simple CQL clarify that the scalar operators have to be interpreted as "at least one value meets the condition" for array values.
• In another conformance class add array operators to allow more fine-grained control.

The additional conformance class could also be moved to another part, but there is a risk that different implementations will implement array support in different ways.

[cportele]

Meeting 2020-10-12: Agreement to add it in a separate conformance class. @pvretano will update the BNF first so we can agree on the syntax before writing the conformance class. Start with set operators (subset, superset, overlaps, equal).

[pvretano]

Meeting: 2020-10-26: Reviewed proposed BNF and it looks fine. Peter to create pull request with the complete text of the ARRAYS requirement class.

[jisantuc]

Thanks y'all -- a separate conformance class that we can advertise on the API's landing page should work great for us

[cportele]

Meeting 2020-11-09: The PR is ready and will be pushed soon by @pvretano .

[cportele]

Meeting 2020-11-23: Work on the examples identified a potential issue in the JSON encoding which would result in duplicate keys (even if it would be ok for the original ECMA JSON spec, but it would create many issues with implementations). @pvretano will push the current state including with the issue that he has identified and create a new issue for this.

[cportele]

Reopen for review in SWG meeting on 2020-12-07