MySQL To Mongodb query issue

Question

I want to thank everyone who has helped me in the past as well as your time to read this post.

I have an issue with converting my sql query to mongodb query.

SQL Query:

SELECT m_time,m_latency FROM pkt_tbl WHERE (m_time-m_latency+" . LATENCY_DELTA . ")>=" . $rangeXFrom . " AND (m_time-m_latency+" . LATENCY_DELTA . ")<=" . $rangeXTo . " ORDER BY (m_time-m_latency+" . LATENCY_DELTA . ")"

Mongodb Query (My Try)

$latency_delta_split = array('m_time-m_latency'=>LATENCY_DELTA);
$find_query = array($latency_delta_split=>array('$gte'=>$rangeXFrom),$latency_delta_split=>array('$lte'=>$rangeXTo));
$find_projectins = array('m_time'=>1, 'm_latency'=>1);    $find_query = array(('m_time-m_latency'=>LATENCY_DELTA)=>array('$gte'=>$rangeXFrom),('m_time-m_latency'=>LATENCY_DELTA)=>array('$lte'=>$rangeXTo));

I am still getting an error and couldn't really figure out the solution.

What I have done:

1] Read Mongodb Docs.
2] Searched on StackOverflow for similar problems. (None addressed mine)
3] Refered to PHP.NET official docs.

However, I couldn't figure out, Help would be appreciated.

the query does not run. And I am pretty much sure there's an error in the query. (I did not do the ORDER BY yet but the query which I have written has errors in it that's for sure. If someone can help me point out that would be great. — iron man
– iron man, Commented Jul 18, 2013 at 0:26
Yes, but what SPECIFIC error are you getting. When you call Mondo and you get no results, are you looking at what error you are getting (i.e. something more specific than "it doesn't work")? — Mike Brant
– Mike Brant, Commented Jul 18, 2013 at 0:28
It says the following Multiple annotations found at this line: - syntax error, unexpected ',' - syntax error, unexpected '=>' But I am pretty sure that my the syntax i wrote is incorrect. Can you please correct my syntax. — iron man
– iron man, Commented Jul 18, 2013 at 0:34

Derick · Accepted Answer · 2013-07-18 07:51:51Z

In your SQL you have: m_time-m_latency which I presume means subtract one column from another.

The short answer:

This is not something you can represent with MongoDB queries yet as you can only compare fields with static values.

The long answer:

If you want to find documents where m_time - m_latency + LATENCY_DELTA is in a specific range then you will have to store that value pre-calculated in another field of the document. If you do that, then you can simply run the query with:

db.collection.find( { 'm_calculated_latency' : { '$gte' : FROM_RANGE, '$lte' : TO_RANGE } } );

Or in PHP:

$collection->find( array(
    'm_calculated_latency' => array(
        '$gte' => $from_range,
        '$lte' => $to_range,
    )
)

The workaround:

With MongoDB's aggregation framework, you can probably do a query like you want, but it will by far not be the fastest or most elegant solution and neither does it use an index. So please redesign your schema and add that pre-calculated field.

With the warning out of the way, here goes:

FROM=3
TO=5
DELTA=1
db.so.aggregate( [ 
    { $project: { 
        'time': { $add: [
            { $subtract: [ '$m_time', '$m_latency' ] },
            DELTA
        ] },
        'm_time' : 1,
        'm_latency' : 1
    } }, 
    { $match: { 'time' : { $gte: FROM, $lte: TO } } },
    { $sort: { 'time' : 1 } }
] );

In the $project step, we're calculating the field time as m_time - m_latency + DELTA. We also output the original m_time and m_latency fields. And then in the $match step, we are comparing the calculated time with either FROM or TO. And lastly we sort by the calculated time. (As your original SQL sort didn't make sense either, I assumed you meant to sort by the time difference).

With my input data:

> db.so.insert( { m_time: 5, m_latency: 3 } );
> db.so.insert( { m_time: 5, m_latency: 1 } );
> db.so.insert( { m_time: 8, m_latency: 1 } );
> db.so.insert( { m_time: 8, m_latency: 3 } );
> db.so.insert( { m_time: 7, m_latency: 2 } );
> db.so.insert( { m_time: 7, m_latency: 4 } );
> db.so.insert( { m_time: 7, m_latency: 6 } );
> FROM=3
> TO=5
> DELTA=1

This produces:

{
    "result" : [
        {
            "_id" : ObjectId("51e7988af4f32a33dac184e8"),
            "m_time" : 5,
            "m_latency" : 3,
            "time" : 3
        },
        {
            "_id" : ObjectId("51e7989af4f32a33dac184ed"),
            "m_time" : 7,
            "m_latency" : 4,
            "time" : 4
        },
        {
            "_id" : ObjectId("51e7988cf4f32a33dac184e9"),
            "m_time" : 5,
            "m_latency" : 1,
            "time" : 5
        }
    ],
    "ok" : 1
}

Now the last trick is to write the aggregate query from above in PHP syntax, which as you can see is quite trivial:

<?php
$m = new MongoClient;
$db = $m->test;

$r = $db->so->aggregate( [ 
    [ '$project' =>  [
        'time' => [ '$add' => [
            [ '$subtract' => [ '$m_time', '$m_latency' ] ],
            $DELTA
        ] ],
        'm_time' => 1,
        'm_latency' => 1
    ] ], 
    [ '$match' => [ 'time' => [ '$gte' => $FROM, '$lte' => $TO ] ] ],
    [ '$sort' => [ 'time' => 1 ] ]
] );
var_dump( $r );
?>

Mike Brant · Accepted Answer · 2013-07-18 00:40:55Z

0

This makes no sense. You can't chain together => when specifying key value pairs in cases like this:

('m_time-m_latency'=>LATENCY_DELTA)=>array('$gte'=>$rangeXFrom)

I honestly am not sure what you are trying to do there so as to give better advice.

answered Jul 18, 2013 at 0:40

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

1 Comment

iron man Over a year ago

Thank you for your response. I have a SQL query, I am trying to write a mongodb query for that. I have written few simple one's but this one looks complicated. Don't know how to tackle that.

Collectives™ on Stack Overflow

MySQL To Mongodb query issue

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related