pymongo: issues using $lte and $gte

[aliane abdelouahab]

hi, it seems that:

list(db.users.find({"produit_up.spec.prix":{"$gte":0, "$lte":
1000000}}, {"_id":0,"produit_up":1}))

and

list(db.users.find({"produit_up.spec.prix":{"$gte":0, "$lte":1000}},
{"_id":0,"produit_up":1}))

give the same result (in the logic, it will give only one result, we
have three values: 1000, 20000, 500000):

[{u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',
u'description': u"test",
u'id': u'alucaard134901952647',
u'namep': u'nokia 3310',
u'nombre': 1,
u'prix': 1000,
u'tags': [u'portable', u'nokia', u'3310'],
u'vendu': False}},
{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('50686d013a5f3a04a8923b3e')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',
u'description': u'test',
u'namep': u'iphone 3gs',
u'nombre': 1,
u'prix': 20000,
u'tags': [u'iphone', u'3gs', u'apple'],
u'vendu': False}},
{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'paysage',
u'photo': ObjectId('50686d3e3a5f3a04a8923b40')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',
u'description': u'test',
u'id': u'alucaard134902099082',
u'namep': u'peugeot 206',
u'nombre': 1,
u'prix': 500000,
u'tags': [u'voiture', u'206', u'hdi'],
u'vendu': False}}]}]

[Bernie Hackett]

>>> list(db.users.find({"produit_up.spec.prix":{"$gte":0, "$lte": 1000000}}, {"_id":0,"produit_up":1}))

[{u'produit_up': {u'spec': {u'prix': 1000}}}, {u'produit_up':
{u'spec': {u'prix': 20000}}}, {u'produit_up': {u'spec': {u'prix':
500000}}}]

>>>
>>> list(db.users.find({"produit_up.spec.prix":{"$gte":0, "$lte": 1000}}, {"_id":0,"produit_up":1}))

[{u'produit_up': {u'spec': {u'prix': 1000}}}]

Looks correct to me. What do you get?

> --
> You received this message because you are subscribed to the Google
> Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com
> To unsubscribe from this group, send email to
> mongodb-user...@googlegroups.com
> See also the IRC channel -- freenode.net#mongodb

[aliane abdelouahab]

i'll check this (ive no internet connexion at home, so i just copy the
code now and i try it..) but have never seen this tip before :o

[aliane abdelouahab]

am still getting the same result!
it's like mongodb dont get the numbers! it worls only when using a
value that is smaller than the upper value!

i'm using Mongodb 2.2 64bits on win7, driver: latest pymongo.

[aliane abdelouahab]

list(db.users.find({"produit_up.spec.prix":{"$gte":0, "$lte": 1000}},
{"_id":0,"produit_up":1}))

Out[5]:

[{u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',

u'description': u"portable tr\xe8s solide, peu servi, avec
batterie d'une autonomie de 3 heures.",

u'id': u'alucaard134901952647',
u'namep': u'nokia 3310',
u'nombre': 1,
u'prix': 1000,
u'tags': [u'portable', u'nokia', u'3310'],
u'vendu': False}},
{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('50686d013a5f3a04a8923b3e')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',

u'description': u'\u0646\u0628\u064a\u0639 \u0623\u064a
\u0641\u0648\u0646 \u062c\u062f\u064a\u062f \u0641\u064a
\u0627\u0644\u0628\u0648\u0627\u0637 \u0645\u0639\u0627\u0647
\u0634\u0627\u0631\u062c\u0648\u0631 \u062f\u0648\u0631\u064a\u062c
\u064a\u0646',
u'id': u'alucaard134902092967',

u'namep': u'iphone 3gs',
u'nombre': 1,
u'prix': 20000,
u'tags': [u'iphone', u'3gs', u'apple'],
u'vendu': False}},
{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'paysage',
u'photo': ObjectId('50686d3e3a5f3a04a8923b40')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',

u'description': u'vends 206 toutes options 2006 hdi.',

u'id': u'alucaard134902099082',
u'namep': u'peugeot 206',
u'nombre': 1,
u'prix': 500000,
u'tags': [u'voiture', u'206', u'hdi'],
u'vendu': False}}]}]

list(db.users.find({"produit_up.spec.prix":{"$gte":0, "$lte": 100}},

{"_id":0,"produit_up":1}))

Out[6]: []

pymongo.version
Out[8]: '2.3+'

[aliane abdelouahab]

it's not the driver's issue, it's mongodb, am in the shell and gives
me the same result!

db.version()
2.2.0

[aliane abdelouahab]

the explication i've is that pymongo make somthing like: verify that a
value that is in that range existe at least in one field, and return
the whole document and not the sub document.

[Bernie Hackett]

PyMongo just sends the query to MongoDB and reads a result, it does
not execute the query itself.

To get the behavior I think you're asking for see the $elemMatch operator:

http://docs.mongodb.org/manual/reference/projection/elemMatch/

[aliane abdelouahab]

but wht the simplest x<value<y dont work?
i found in lot of examples they use it, so is it deprecated?

[Bernie Hackett]

You have three values for "produit_up.spec.prix", 1000, 20000, 500000.
Why would you think that {"$gte":0, "$lte": 100} would match any of
those values? 100 is less than all of those values.

The reason that {"$gte":0, "$lte": 1000} returns all three documents
is that they are all subdocuments in an array. Since one of the
subdocuments in the array is matched **the entire enclosing document**
is a match for your query. Since you did a projection on only
"produit_up", just that array (including all array members) is
returned. Use $elemMatch in MongoDB 2.2 to only return the exact
matching array element.

MongoDB and PyMongo are working as designed here.

[aliane abdelouahab]

ah! now i understand, it's like using the $ when quering only one
element in a list.
so the old queries speaks always about the root document and not about
the subdocuments!
thank you :)

[aliane abdelouahab]

here i'm again
sorry, because i dont know how to use this new $elemMatch when it is
combined with $gt and $lt ?
tried this

list(db.users.find({"produit_up.spec.prix":{" $elemMatch":{"$gte":0,

"$lte": 1000}}}, {"_id":0,"produit_up":1}))

and it returns error!

how do i use it?

[Bernie Hackett]

Reading the docs I linked in my last message
(http://docs.mongodb.org/manual/reference/projection/elemMatch/) shows
that $elemMatch has to be used in the projection:

>>> pprint(list(db.users.find({"produit_up.spec.prix": {"$gte": 0, "$lte": 1000}}, {"_id": 0,"produit_up": {"$elemMatch": {"spec.prix": {"$gte": 0,"$lte": 1000}}}})))

[{u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',

u'description': u'test',

u'id': u'alucaard134901952647',
u'namep': u'nokia 3310',
u'nombre': 1,
u'prix': 1000,
u'tags': [u'portable', u'nokia', u'3310'],

u'vendu': False}}]}]

This also requires MongoDB 2.2.x. It will not work in earlier versions.

[aliane abdelouahab]

thank you very much :D
am sorry because i've not passed the SQL word to understand the
projection :p

[aliane abdelouahab]

the result in only one element? am i doing something wrong? it seems
that i gets the first element found then exists, do i make a loop?

it returns only one element!

list(db.users.find({"produit_up.spec.prix": {"$gte": 0, "$lte":

100000000}}, {"_id": 0,"produit_up": {"$elemMatch": {"spec.prix":
{"$gte":0,"$lte": 100000000}}}}))

Out[16]:

[{u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',

u'description': u"portable tr\xe8s solide, peu servi, avec

batterie d'une autonomie de 3 heures.",

u'id': u'alucaard134901952647',
u'namep': u'nokia 3310',
u'nombre': 1,
u'prix': 1000,
u'tags': [u'portable', u'nokia', u'3310'],
u'vendu': False}}]}]

db.users.find({"produit_up.spec.prix": {"$gte": 0, "$lte":
100000000}}, {"_id": 0,"produit_up": {"$elemMatch": {"spec.prix":
{"$gte":0,"$lte": 100000000}}}}).count()
Out[17]: 1

how do i get the value 3 ?

On 2 oct, 06:36, Bernie Hackett <ber...@10gen.com> wrote:

[Asya Kamsky]

Aliane,

You need to use $elemMatch in the query part (the first JSON document passed to find).

list(db.users.find({"produit_up":{"$elemMatch" : {"$gte": 0, "$lte": 

100000000}}}, {"_id": 0,"produit_up": {"$elemMatch": {"spec.prix": 
{"$gte":0,"$lte": 100000000}}}})) 

You probably don't need to use it in the projection part (second JSON document) if you want the entire array to be returned.

Asya

[aliane abdelouahab]

amsorry, but dont get the idea?

> > > >> > To post to this group, send email to mongod...@googlegroups.com<javascript:>

> > > >> > To unsubscribe from this group, send email to

> > > >> > mongodb-user...@googlegroups.com <javascript:>

> > > >> > See also the IRC channel -- freenode.net#mongodb
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > > > Groups "mongodb-user" group.

> > > > To post to this group, send email to mongod...@googlegroups.com<javascript:>

> > > > To unsubscribe from this group, send email to

> > > > mongodb-user...@googlegroups.com <javascript:>

[Asya Kamsky]

I'm sorry I was responding to part of the last message and not the whole thread.

The point I was trying to make was to make a distinction between $elemMatch for the query part (first JSON document passed to "find") and $elemMatch for projection (second JSON document passed to find).

$elemMatch  in the query document is used to match an element of an array in order to select which entire document to return.

$elemMatch  in the projection is used to decide which elements of the array of the selected document to display/return to you (rather than returning the entire array with the document).

Asya

P.S. I'll see if I can create an example to illustrate.

[aliane abdelouahab]

sowhy then it dont return all values matched, why only the first value
then it exits?

[Sam Millman]

This is a (quirky) behavioural trait that has been noticed in the positional return, that it does only return the first one atm.

I am not sure exactly why it was made like that however the aggregation frameworks $project operator might be able to handle this.

[Asya Kamsky]

It's possible that something got lost in the discussion   :)

If you want it to return the entire array, simply don't use $elemMatch in your projection.

Your original query was:

db.users.find({"produit_up.spec.prix":{"$gte":0, "$lte": 100}},

                         {"_id":0,"produit_up":1})

I think you originally got the answer about why all prices were returned and not just the ones that matched your condition(s).

But I think you asking about why a document was returned when it didn't SEEM  to match the condition(s) you were passing?

Example:

db.em2.find().pretty()

{

"_id" : 1,

"prod" : [

{

"price" : 10

},

{

"price" : 100

}

]

}

db.em2.find({"prod.price":{$gt:50}})

{ "_id" : 1, "prod" : [ { "price" : 10 }, { "price" : 100 } ] }

BUT

db.em2.find({"prod.price":{$gt:50,$lt:11}})

{ "_id" : 1, "prod" : [ { "price" : 10 }, { "price" : 100 } ] }

What?  How can price be >50 AND <11???   Well, this document has price=100 and also price=10 (one is >50 and the other is <11).

If you wanted to COMBINE the conditions then you use $elemMatch in the query.

db.em2.find({"prod":{$elemMatch:{price:{$gt:50,$lt:11}}}})

(nothing returned)

So back to your case - your query was give me all the prod documents where price is greater than 0.

That's all the documents.   Also you wanted to match where price was less than some value and that didn't matter because all the documents were already returned.

So you need to use $elemMatch in the query part.

If you then want the entire array returned, then you do not need to use $elemMatch in the projection part.

Asya

[aliane abdelouahab]

so how to make a solution to show me the number of mathed products
that are between max and min?

[aliane abdelouahab]

can you please provide me the way to do it? and this will take a lot
of time if i'll use a blocking query?

On 2 oct, 14:20, Sam Millman <sam.mill...@gmail.com> wrote:
> This is a (quirky) behavioural trait that has been noticed in the
> positional return, that it does only return the first one atm.
>
> I am not sure exactly why it was made like that however the aggregation
> frameworks $project operator might be able to handle this.
>

[Asya Kamsky]

You would then use $elemMatch in the find clause *and* use $elemMatch in the projection clause.

Do you really need to include $gt:0 ?   I would think that the price is always greater than 0, no?

Continuing building on my example though:

// changing the condition to something reasonable, price between 50 and 110

db.em2.find({"prod":{$elemMatch:{price:{$gt:50,$lt:110}}}})

{ "_id" : 1, "prod" : [ { "price" : 10 }, { "price" : 100 } ] }

// use projection to only get back price array

db.em2.find({"prod":{$elemMatch:{price:{$gt:50,$lt:110}}}},{_id:0, prod:1})

{ "prod" : [ { "price" : 10 }, { "price" : 100 } ] }

// use projection to only get back price array element that matches the condition passed.  NOTE: only the first matching array element would be returned

db.em2.find({"prod":{$elemMatch:{price:{$gt:50,$lt:110}}}},{_id:0, prod:{$elemMatch:{price:{$gt:50,$lt:110}}}})

{ "prod" : [ { "price" : 100 } ] }

Asya

[aliane abdelouahab]

about using the zero value, it's just to see if there is not bug, it's
something like debugging (if the whole expression is evaluated).
am sorry, if i dont get the idea clearly, why not making an iterator
for the elementMatch?

> ...
>
> plus de détails »

[Asya Kamsky]

While it's true that projection with $elemMatch will only return the first matching array element,

and you can use aggregation framework to return back all such elements, it's not $project that you need.

You need $unwind, $match and $group.

New data:

db.em2.find().pretty()

{

"_id" : 1,

"prod" : [

{

"price" : 10

},

{

"price" : 80

},

{

"price" : 100

}

]

}

db.em2.aggregate([{$unwind:"$prod"}, {$match:{"prod.price":{$gt:50,$lt:110}}}, {$group:{_id:"$_id", prod:{$push:"$prod"}}}])

{

"result" : [

{

"_id" : 1,

"prod" : [

{

"price" : 80

},

{

"price" : 100

}

]

}

],

"ok" : 1

}

But original query:

db.em2.find({"prod":{$elemMatch:{price:{$gt:50,$lt:110}}}},{_id:0, prod:{$elemMatch:{price:{$gt:50,$lt:110}}}})

{ "prod" : [ { "price" : 80 } ] }

[aliane abdelouahab]

so i think the best solution i'll do is using the python dictionnary
to search that element, because the aggregation method will make a lot
of time.
it's like sorting the elements: aggregation VS python way, the python
way is faster?

> > On 2 October 2012 14:02, aliane abdelouahab <alabde...@gmail.com<javascript:>

[Asya Kamsky]

I would not necessarily assume that this will be faster in Python than Mongo (though that's likely to be true if most of your documents are like the one you posted).

If the size of the array is relatively small then the query will be fast and it will be simple in Python to only operate on the returned list elements that matched your condition.

It should be pretty easy to try both and see how fast they work, no?

Asya

[aliane abdelouahab]

yes, i'll first try your solution, and then break my head with the
python's one :p
but now, since there is Motor (Async Pymongo for Tornado) this will
not make problem?

> ...
>
> plus de détails »

[aliane abdelouahab]

i tried it using python "list comprehension" and it seems it's the "$"
that dont incremenet: he just find on result and stops.

[i for i in
db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.prix")
if i<=20000]

[1000, 20000]

list(db.users.find({"produit_up.spec.prix":{"$in":[i for i in
db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.prix")
if i<=20000]}}, {"produit_up.$":1}))

[{u'_id': ObjectId('5061fab93a5f3a09f4be0e21'),

u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',
u'description': u"portable tr\xe8s solide, peu servi, avec
batterie d'une autonomie de 3 heures.",
u'id': u'alucaard134901952647',
u'namep': u'nokia 3310',
u'nombre': 1,
u'prix': 1000,
u'tags': [u'portable', u'nokia', u'3310'],
u'vendu': False}}]}]

and it gives me the same thing as:

list(db.users.find({"produit_up.spec.tags":{"$in":["nokia"]}},
{"produit_up.spec.$":1, "_id":0,"personnel.pseudo":1,"produit_up":
1,"adresse.commune":1}))

[{u'adresse': {u'commune': u'Azazga'},
u'personnel': {u'pseudo': u'alucaard'},

u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',
u'description': u"portable tr\xe8s solide, peu servi, avec
batterie d'une autonomie de 3 heures.",
u'id': u'alucaard134901952647',
u'namep': u'nokia 3310',
u'nombre': 1,
u'prix': 1000,
u'tags': [u'portable', u'nokia', u'3310'],
u'vendu': False}}]}]

confirmed: the positional element is good for returning one element
when you have subdocument elements.

but here is the bizarre thing:

list(db.users.find({"produit_up.spec.prix":{"$in":[i for i in
db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.prix")
if i<=20000]}}, {"produit_up.something_that_dont_existe_blablablabla.
$":1}))

[{u'_id': ObjectId('5061fab93a5f3a09f4be0e21'),

u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
u'orientation': u'portrait',
u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
u'spec': {u'abus': 0,
u'date': u'2012-09-30',
u'description': u"portable tr\xe8s solide, peu servi, avec
batterie d'une autonomie de 3 heures.",
u'id': u'alucaard134901952647',
u'namep': u'nokia 3310',
u'nombre': 1,
u'prix': 1000,
u'tags': [u'portable', u'nokia', u'3310'],
u'vendu': False}}]}]

so it looks only for the name of the sub doucment, and dont seek for
its attributes (dont veritfy if what is after the dot exists)?

but if i write:

list(db.users.find({"produit_up.spec.prix":{"$in":[i for i in
db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.prix")
if i<=20000]}}, {"produitup.something_that_dont_existe.$":1}))

i got an error: OperationFailure: database error: Positional operator
does not match the query specifier.

so i think i learn aggregation!

here is what i get:

db.users.aggregate([{"$unwind":"$produit_up"},{"$match":
{"produit_up.spec.prix":{"$gte":1000,"$lte":20000}}}, {"$group":
{"_id":"$_id","produit_up":{"$push":"$produit_up.spec.prix"}}}])

Out[9]:
{u'ok': 1.0,
u'result': [{u'_id': ObjectId('5061fab93a5f3a09f4be0e21'),
u'produit_up': [1000, 20000]}]}

db.users.aggregate([{"$unwind":"$produit_up"},{"$match":
{"produit_up.spec.prix":{"$gte":1000,"$lte":20000}}}, {"$group":
{"_id":"$_id","produit_up":{"$push":"$produit_up.spec.namep"}}}])

Out[13]:
{u'ok': 1.0,
u'result': [{u'_id': ObjectId('5061fab93a5f3a09f4be0e21'),
u'produit_up': [u'nokia 3310', u'iphone 3gs']}]}

i love that ;)

> ...
>
> plus de détails »

[Asya Kamsky]

I'm a little confused now.  You are using the positional operator here - before we were discussing the $elemMatch operator...

Anyway, aggregation framework is great, I'm sure you will find many uses for it, whether here or somewhere else.

[aliane abdelouahab]

the positional element is just to apply the example to search an
element in tag liste, else, i'll get the whole document.
am i confused?

On 2 oct, 23:47, Asya Kamsky <a...@10gen.com> wrote:
> I'm a little confused now.  You are using the positional operator here -
> before we were discussing the $elemMatch operator...
>
> Anyway, aggregation framework is great, I'm sure you will find many uses
> for it, whether here or somewhere else.
>
>
>
>
>
>
>
> On Tuesday, October 2, 2012 5:36:46 PM UTC-4, aliane abdelouahab wrote:
>
> > i tried it using python "list comprehension" and it seems it's the "$"
> > that dont incremenet: he just find on result and stops.
>
> > [i for i in

> > db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.pr ix")

>
> > if i<=20000]
>
> > [1000, 20000]
>
> > list(db.users.find({"produit_up.spec.prix":{"$in":[i for i in

> > db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.pr ix")

> > db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.pr ix")

>
> > if i<=20000]}}, {"produit_up.something_that_dont_existe_blablablabla.
> > $":1}))
>
> > [{u'_id': ObjectId('5061fab93a5f3a09f4be0e21'),
> >   u'produit_up': [{u'avatar': {u'avctype': u'image/jpeg',
> >      u'orientation': u'portrait',
> >      u'photo': ObjectId('506867863a5f3a0ea84dcd6c')},
> >     u'spec': {u'abus': 0,
> >      u'date': u'2012-09-30',
> >      u'description': u"portable tr\xe8s solide, peu servi, avec
> > batterie d'une autonomie de 3 heures.",
> >      u'id': u'alucaard134901952647',
> >      u'namep': u'nokia 3310',
> >      u'nombre': 1,
> >      u'prix': 1000,
> >      u'tags': [u'portable', u'nokia', u'3310'],
> >      u'vendu': False}}]}]
>
> > so it looks only for the name of the sub doucment, and dont seek for
> > its attributes (dont veritfy if what is after the dot exists)?
>
> > but if i write:
>
> > list(db.users.find({"produit_up.spec.prix":{"$in":[i for i in

> > db.users.find({"personnel.pseudo":"alucaard"}).distinct("produit_up.spec.pr ix")

> ...
>
> plus de détails »