JOIN operation and IN-FILTER predicates

[Evandro K]

Hello,

I´m testing NotaQL and I had some problems. I have two questions:

1) Can I execute a JOIN operation between two or more MongoDB collections using a NotaQL script? For example, if  I have a Customer collection and a Order collection where the Order collection has customer_id. Can I perform a kind of JOIN operation where a Customer document will be inserted in Order document?

2) I tried to use two or more predicates in "IN-FILTER" clause, is it possible?. See my script:

        IN-ENGINE: mongodb(database_name <- 'test', collection_name <- 'restaurants'),
        OUT-ENGINE: mongodb(database_name <- 'test', collection_name <- 'res'),
        IN-FILTER: IN.grades[*].score > 90 AND IN-FILTER: IN.cuisine = 'Indian',
        OUT._id <- IN._id,"
        OUT.$(IN.*.name()) <- IN.@;

This transformation script throws:

line 1:184 no viable alternative at input 'IN-ENGINE:mongodb(database_

name<-'test',collection_name<-'restaurants'),OUT-ENGINE:mongodb(database_name<-'test',collection_name<-'res'),IN-FILTER:IN.grades[*].score>90,IN-FILTER'
rule stack: [notaql]
context:  (class notaql.parser.antlr.NotaQL2Parser$NotaqlContext)
Exception in thread "main" java.lang.IllegalStateException: notaql.parser.antlr.NotaQL2Parser failed to parse at line 1 at character 184 due to no viable alternative at input 'IN-ENGINE:mongodb(database_name<-'test',collection_name<-'restaurants'),OUT-ENGINE:mongodb(database_name<-'test',collection_name<-'res'),IN-FILTER:IN.grades[*].score>90,IN-FILTER'; Offending symbol: class org.antlr.v4.runtime.CommonToken: 'IN-FILTER'
    at notaql.parser.NotaQLErrorListener.syntaxError(NotaQLErrorListener.java:52)
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:67)
    at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:559)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportNoViableAlternative(DefaultErrorStrategy.java:310)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:147)
    at notaql.parser.antlr.NotaQL2Parser.notaql(NotaQL2Parser.java:134)
    at notaql.parser.NotaQLExpressionParser.parse(NotaQLExpressionParser.java:40)
    at notaql.NotaQL.evaluate(NotaQL.java:116)
    at emk.mongodb.Teste.main(Teste.java:60)
Caused by: org.antlr.v4.runtime.NoViableAltException
    at org.antlr.v4.runtime.atn.ParserATNSimulator.noViableAlt(ParserATNSimulator.java:1597)
    at org.antlr.v4.runtime.atn.ParserATNSimulator.execATN(ParserATNSimulator.java:493)
    at org.antlr.v4.runtime.atn.ParserATNSimulator.adaptivePredict(ParserATNSimulator.java:414)
    at notaql.parser.antlr.NotaQL2Parser.notaql(NotaQL2Parser.java:106)
    ... 3 more


Best,
Evandro

[Johannes]

Hi Evandro,

1) Joins are not possible in the implementation on GitHub. There are multiple possibilities:

a) Implement a MongoDBJoinEngine that takes a database, two collection names, and the two join columns from the two collections. Within the engine, you perform the join via the aggregation-pipeline step $lookup. This is the most performant way, but it only allows for joins between two MongoDB collections.

b) We implemented a generic join engine that takes two other engines as imput and a boolean expression (join predicate). The NotaQL grammar has to be changed for this, so it is not as easy to add as solution a)

c) One design for this problem are so called DelegateEngines. This kind of engine takes engines as parameters (as in b), but basically they can also perform cross products, union, intersection, ...

The easisest way to implement is solution a) because you do not need to change the grammar. The usage would then be something like this: IN-ENGINE: mongodbjoin(db<-'...', col1<-'...', col2<-'...', field1<-'...', field2<-'...'). 

2) The correct usage is as follows: IN-FILTER: IN.grades[*].score > 90 AND IN.cuisine = 'Indian',