[CALCITE-270] Generate complex conditions against MongoDB

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: mongodb-adapter
• Labels: github-import
• Assignee: Unassigned
• Reporter: GitHub Import
• Created: 18/Jun/14 08:56
• Updated: 03/Jan/18 01:57

Description

We need to rework the MongoDB adapter to support complex conditions. Currently we use the `$match` operator whose syntax is limiting because it expects a column name on the LHS and a value on the RHS.

We should instead generate a 'condition' column to be preceding `$project` operator. Then we can generate expressions the conventional way, e.g. `$gt: [e1, e2]` as opposed to `e1: {$gt: e2}`.

In the following example, `cond` holds the evaluated condition, a subsequent `$match` filters on it, then a `$project` projects away the column.

```json
db.zips.aggregate(
{$project: {STATE: '$state', POP: '$pop'}},
{$group: {_id: '$STATE', C: {$sum: 1}, _2: {$sum: '$POP'}}},
{$project: {STATE: '$_id', C: '$C', _2: '$_2', cond: {$gt: [{$cond: [{$eq: ["$C", 0]}, null, "$_2"]}, 12000000]}}},
{$match: {cond: true}},
{$project: {STATE: 1, C: 1}})
```

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/270
Created by: julianhyde
Labels:
Created at: Thu May 01 23:37:10 CEST 2014
State: open

Issue Links

• relates to: CALCITE-2109 Mongo adapter: unable to translate (A AND B) conditional case