#8540: is there a list of our math functions for MongoDB I can included in the MongoDB Query Reference doc?

swissarmyknife(*) reported 2013-03-27T19:14:24Z · last modified 2013-04-06T00:18:32Z

is there a list of our math functions for MongoDB I can included in the MongoDB Query Reference doc?

Dev	emil.goicovici
QA	JennyNishimura

Priority	Low
Complexity	Unknown

Component	MongoSQL
Version	14.0

I made this section

https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/MongoDB/page/Query-Reference/Query-Reference#arithmetic_and_mathematical_functions

but I used MongoDB's list ( http://docs.mongodb.org/manual/reference/aggregation/#arithmetic-operators ) to populate what these functions are.

How do I get a list of our math functions, or should I not have a math functions section?

3 attachments

using + to add

2013-03-27T21:48:21Z

167 KB
MathExpr nosql-jdbc-1.0.4.png

2013-03-29T19:23:27Z

17 KB
Build #: 31855 - functions working

2013-04-06T00:18:32Z

149 KB

All Comments (12) Change History

JennyNishimura 2013-03-27T19:35:53Z

We shouldn't use the MongoDB documentation (docs.mongodb.org) as our reference. We may not have full support of every function in the MongoDB syntax. We support only what makes sense in SQL syntax. We should refer to our "MongoSQL Query Reference" document to work on our documentation.

As documented in the MongoSQL Query Reference:

expr | expr

expr & expr

expr << expr

expr >> expr

expr + expr

expr – expr

expr * expr

expr / expr

expr % expr

swissarmyknife(*) 2013-03-27T20:18:06Z

When I attempt

select

(select sum(pop) from zips_editing where city='BROOKLYN')

(select sum(pop) from zips_editing where city='NEW YORK')

I get back this error

>[Error] Script lines: 1-6 --------------------------

Syntax error on input SQL `select

(select sum(pop) from zips_editing where city='BROOKLYN')

(select sum(pop) from zips_editing where city='NEW YORK')`:Math expressions not supported.

am I doing something wrong?

JennyNishimura 2013-03-27T20:59:39Z

You are adding two SELECT statements, which is not valid SQL.

You are trying to add two numbers in two different records. The correct SQL is:
SELECT sum(pop) FROM zips WHERE city IN ('BROOKLYN', 'NEW YORK')

You use the + operator to add two numbers in a record, e.g. SELECT col1 + col2 FROM table

swissarmyknife(*) 2013-03-27T21:47:41Z

I've got two records that are both integer ( itemID, prodID) and attempt to add them using the + as our MongoDB reference indicates

select itemID + prodID from NewItems where dodad='things'

and I still get

>[Error] Script lines: 1-1 --------------------------

Syntax error on input SQL `select itemID + prodID from NewItems`:Math expressions not supported.

see attached

I can successfully execute

select sum(itemID,prodID) from NewItems where dodad='things'

to get the desired result

JennyNishimura 2013-03-27T21:58:15Z

Emil, the math operators are not implemented.

emil.goicovici 2013-03-27T22:43:56Z

select sum(itemID,prodID) from NewItems where dodad='things'

to get the desired result

I doubt that the result is the expected one. The SUM() aggregate function expects a single argument. In your scenario the prodID argument is ignored and the summarization is done just on the itemID field.

For the next drop, the function calls will be more restrictive (i.e. errors are thrown if the number of arguments mismatch the expected one or if parameter types are wrong).

Emil, the math operators are not implemented.

Yes, just a subset of these math expressions are currently supported on the Mongo JDBC and just on WHERE and SET filters (e.g. SET number = number + 10 which is implemented using the $inc operator; WHERE qty % 2 = 0 etc).

At the time this feature was implemented, it seemed that MongoDB Server has no support for queries like select itemID + prodID from NewItems and thus the math operation should be applied at the java level by iterating over each document, which wouldn't provide great performance.

However, now it seems the Aggregation Framework provides this feature. The Mongo Shell equiv for the query above would be

db.NewItems.aggregate({$project: { "sum" : { $add: ["$itemID", "$prodID" ] } } })

I'll add support for it on the next drop.

However, these operators from the Aggregation Framework still has some limitations (expected from implementation perspective but maybe not so obvious to front-end users). For example, given the baseball collection containing documents like:

{ "teamName" : "Cardinals", "city" : "St. Louis", "ranking" : "2", "valuation" : 90, "managerName" : { "first" : "Dave", "last" : "Potter" }, "colors" : [ "green", "white" ], "stats" : [ { "year" : 1999, "wins" : 98, "rbis" : 251, "grade" : "B" }, { "year" : 2012, "wins" : 78, "rbis" : 200, "grade" : "C" }, { "year" : 1967, "wins" : 30, "rbis" : 74, "grade" : "D" } ] }

the select stats.wins + stats.rbis from baseball will translate on the next drop to

db.baseball.aggregate({$project: { "sum": { $add: ["$stats.wins", "$stats.rbis"] } } })

which will throw an

"errmsg" : "exception: $add only supports numeric or date types, not Array"

exception as the aggregation operator cannot work inside arrays.

select sum(itemID,prodID) from NewItems where dodad='things'

to get the desired result

For the next drop, the function calls will be more restrictive (i.e. errors are thrown if the number of arguments mismatch the expected one or if parameter types are wrong).

Emil, the math operators are not implemented.

However, now it seems the Aggregation Framework provides this feature. The Mongo Shell equiv for the query above would be

db.NewItems.aggregate({$project: { "sum" : { $add: ["$itemID", "$prodID" ] } } })

I'll add support for it on the next drop.

the select stats.wins + stats.rbis from baseball will translate on the next drop to

db.baseball.aggregate({$project: { "sum": { $add: ["$stats.wins", "$stats.rbis"] } } })

which will throw an

"errmsg" : "exception: $add only supports numeric or date types, not Array"

exception as the aggregation operator cannot work inside arrays.

emil.goicovici 2013-03-27T22:54:24Z

Jonathan, the MongoDB JDBC embedded inside ADS doesn't support any of these functions you've mentioned on the wiki page:

ADD, DIVIDE, MOD, MULTIPLY, SUBTRACT, CONCAT

For the first five functions I'll implement for the next mongo-jdbc drop the +, /, %, *, - operators that will map on the $add , $divide etc aggregation operators. CONCAT is a string aggregation operator and probably we will support it only if necessary. Please update the wiki page.

swissarmyknife(*) 2013-03-27T23:01:05Z

Thank you for the corrections.

I've taken out all the inappropriate contents of the math section.

emil.goicovici 2013-03-28T17:57:28Z

For the nosql-jdbc drop, math expressions ( + , - , *, / , % ) will be supported on WHAT fields ( e.g. select itemID + prodID from NewItems ) and inside WHERE filters (e.g. select * from NewItems where itemID + prodID < 1000 ).

JennyNishimura 2013-03-29T19:23:27Z

I am using nosql-jdbc 1.0.4. The _id field is always returned with the result set when a math expression is used in the SELECT field list. See attached screenshot "MathExpr nosql-jdbc-1.0.4".

- select pets + children from contacts1
Two columns are returned: pets + children, _id.

- select pets + children as counter from contacts1
Two columns are returned: _id, pets + children.
The alias "counter" is not used in the result set.

emil.goicovici 2013-04-01T14:12:47Z

The _id field is now hidden unless is explicitly listed on queries having math expressions. Aliases are now supported on math expressions.

swissarmyknife(*) 2013-04-06T00:15:46Z

fixed in

Version: 14.0-dev-34

Build #: 31855

Search Tips

Aqua Data Studio / nhilam

is there a list of our math functions for MongoDB I can included in the MongoDB Query Reference doc?

3 attachments

Issue #8540

Completion

Search Tips

Aqua Data Studio / nhilam

Title

is there a list of our math functions for MongoDB I can included in the MongoDB Query Reference doc?

3 attachments

Issue #8540

Completion