array length support

[LinusU]

First of all, thanks for an awesome lib!

One small feature request that I would love to have would be to support the $size operator.

Some examples:

Empty array

{ where: { fruits: { $size: 0 } } }

coalesce(array_length(fruits, 1), 0) = 0

Non-empty array

{ where: { fruits: { $size: { $gt: 0 } } } }

coalesce(array_length(fruits, 1), 0) > 0

Exactly 8 entries

{ where: { fruits: { $size: 8 } } }

coalesce(array_length(fruits, 1), 0) = 8

[jrf0110]

Very good idea! My initial idea that you could implement without waiting on a mongo-sql version publish is to do something like this:

http://mosql.j0.hn/#/snippets/8i

{ type: 'select'
, table: 'grocery_stores'
, where: {
    fruits: {
      $size: {
        operation: '>'
      , value: 5
      }
    }
  }
}

This works within the existing limitations of the library.

Ideally, the conditional clause building engine would be more robust to allow the exact DSL you provided. Unfortunately, that code was written quite a long time ago and this library is due for a version 3 rewrite.

[LinusU]

I would love to see a version 3 🎉

[jrf0110]

Me, too.

I'd like to see more rigidity in the API so there's never ambiguity during introspection (e.g. looking up columns in a query, but not knowing if someone used strings or object notation). Would also love to see the conditional engine split in two: Expressions and Operators. Right now there's a deep coupling between the two and the only way to get around it is to specify $custom: [ 'some_fn( some_column ) = some_other_thing( $1 )', 'foo' ]. There are also seems to be some interest in parsing the SQL AST from a string and converting that MongoSQL (which, is in itself a kind of a AST).

The whole point of the library (I've come to realize) is to always have a programmatic API on your SQL. But balancing that with grokability is a difficult thing to do (I suppose readability/grokability would be the job of the data-access layer or ORM built on top of MoSQL).

Anyway, a v3 will be a difficult thing. I built MoSQL because Goodybag needed a better SQL string building library. Given that the current version works decently well 99% of the time, it's hard to dedicate time to it.

I'd love to hear your thoughts on what you'd like to see in v3.