1

I have following SQL query:

SELECT SUM("PAYMENT_HISTORY"."AMOUNT_PAID") FROM "PAYMENT_HISTORY" WHERE (("PAYMENT_HISTORY".YEAR >= 2011) AND ("PAYMENT_HISTORY".YEAR <= 2013) AND ("PAYMENT_HISTORY"."AMOUNT_PAID" >= 500.0))

same SQL after conversion was ran from MongoDB shell and it returns value.

db.PAYMENT_HISTORY.aggregate( { $match : {$and: [{YEAR: {$gte : 2011}}, {YEAR: {$lte : 2013}}, {AMOUNT_PAID: {$gte: 500.00}}]}}, { $group : {_id : "POLICY_ID", total:{$sum : "$AMOUNT_PAID"}}} )

While i am trying to run through MongoDB C driver, it doesn't return any results.

int status = mongo_client( mNoSQLConnection, "127.0.0.1", 27017 );
if (status != MONGO_OK){
    switch (mNoSQLConnection->err) {
        case MONGO_CONN_NO_SOCKET:  printf( "no socket\n" ); 
        case MONGO_CONN_FAIL:       printf( "connection failed\n" ); 
        case MONGO_CONN_NOT_MASTER: printf( "not master\n" ); 
    }
}
else{
    bson query[1], b_result[1];
    mongo_cursor cursor[1];
    bson_init( query );
        bson_append_string(query, "aggregate", "PAYMENT_HISTORY");
            bson_append_start_object(query, "$match");
                bson_append_start_array(query, "$and");
                        bson_append_start_object(query, "$gte");
                            bson_append_int(query, "YEAR", 2011);
                        bson_append_finish_object(query);
                        bson_append_start_object(query, "$lte");
                            bson_append_int(query, "YEAR", 2013);
                        bson_append_finish_object(query);
                        bson_append_start_object(query, "$gte");
                            bson_append_double(query, "AMOUNT_PAID", 500.00);
                        bson_append_finish_object(query);
                bson_append_finish_array(query);
            bson_append_finish_object(query);
            bson_append_start_object(query, "$group");
                bson_append_string(query, "_id", "POLICY_ID");
                bson_append_string(query, "total", "total");
                    bson_append_start_object(query, "$sum");
                        bson_append_start_object(query, "$AMOUNT_PAID");
                        bson_append_finish_object(query);
                    bson_append_finish_object(query);
            bson_append_finish_object(query);
    bson_finish( query );

    bson_print(query);

    int status1;
    status1 = mongo_run_command(mNoSQLConnection, "DB_NAME", query, b_result);
    if (MONGO_OK != status1 )
    {
    }
    else
    {
        /*command results*/
        bson_print(b_result);
    }

    bson_destroy( query );
    mongo_cursor_destroy( cursor );

}

Can somebody help me in figuring out mistake/s in above code?

Thanks

Manish
  • 41
  • 6
  • What is the output of `bson_print`? – WiredPrairie Feb 10 '14 at 12:16
  • 1
    Right before the first `$match`, you need to define an array with the pipeline: `bson_append_start_array(b, "pipeline");` – WiredPrairie Feb 10 '14 at 12:21
  • @WiredPrairie output of bson_print is as and will update this after trying pipeline: ` aggregate : 2 PAYMENT_HISTORY $match : 3 $and : 4 $gte : 3 YEAR : 16 2011 $lte : 3 YEAR : 16 2013 $gte : 3 AMOUNT_PAID : 1 500.000000 $group : 3 _id : 2 POLICY_ID total : 2 total $sum : 3 $AMOUNT_PAID : 3 ` – Manish Feb 11 '14 at 06:36
  • Did you try adding the pipeline element? – WiredPrairie Feb 11 '14 at 11:51
  • Despite the detractors to what I believe is a fair answer I encourage you to look at [BCON](http://api.mongodb.org/c/current/bcon.html) as a way of improving the legibility of what you are doing. It's structure is a lot more analogous to the pure shell syntax you gave in your question. – Neil Lunn Feb 11 '14 at 12:58
  • @WiredPrairie Thanks for direction. I tried pipeline aswell but Mongo DB crashed with following error with pipeline: `Wed Feb 12 06:30:03 [initandlisten] connection accepted from 127.0.0.1:54299 #1 (1 connection now open) Wed Feb 12 06:30:03 [initandlisten] connection accepted from 127.0.0.1:54300 #2 (2 connections now open) Wed Feb 12 06:30:03 [conn1] ERROR: Uncaught exception, terminating Wed Feb 12 06:30:03 dbexit: ` I will read again about aggregation in MongoDB, will re-write code and update this. – Manish Feb 12 '14 at 01:02
  • I'd like to help further, but since it's such a bear to get the C driver compiled on Windows, I just can't do any testing like I'd normally do to try to help. (Sorry). – WiredPrairie Feb 12 '14 at 01:08

0 Answers0