Enable complex query filters #373
Description
Riffing off of #366, we've been discussing a more advanced filter mechanism in entity mappings both for queries and for schema assignment. I'm sketching this out here to get some feedback.
The basic idea is to make it possible to compile nested complex queries. This might have to be incompatible to the existing filter, filter_not rules in mappings, so we would be introducing a new query section (which filter and filter_not would be transparently re-written to). The query syntax itself could riff off of the MongoDB syntax, which manages to encapsulate compound queries in JSON.
The second purpose of this enhancement would be to make schema conditional on column contents. For example, we often see tables where companies and people are mixed, and whether a row describes a person or company is determined by a value like individual/entity in one column.
Here's a sketch of how we could address both issues:
query:
table: zz_donors
query:
$and:
country: xk
$or:
political_party:
$like: "%conservative%"
district: ["Southern", "Northern"]
entities:
donor:
schema_query:
Person:
donor_type: "individual"
Company:
donor_type: ["corporate", "business"]Especially keen for feedback from @uhhhuh @brrttwrks :)