Re: nested query

[ls]

I already tried to translate a sql statement which would be:

select publishing_company from (select * from database where pages >= 500) foo group by 1 having count(*) >= 2 and sum(pages) >= 500

in a translater but the problem is that no subqueries are allowed...

Am Montag, 20. Mai 2013 15:58:04 UTC+2 schrieb ls:

Hi guys

I have a Problem:

I have a dataset within the following format:

{
     "book": "TITLE"
     "pages": 560
     "publishing company": "PUBLISHER"
...
}

Now I want to create a query, which will show me all publishing companies that have at least 2 books with pages > 500.

Can anyone help me?

Thanks

[Gary R]

Aggregation pipeline could do this if you like.
$match for docs with pages>500, then $group by _id being publishing company and create counter with $sum:1, then $match for your counter greater than 1.

Gary

[ls]

OK, now I have:

db.YYY.aggregate(
    { $match : { pages : {$gte : 500} },

$group : {
    _id : "$publishing_company",
    total_books : {$sum : 1} },


$match : { total_books : {$gte : 2} } }

);

but there aren't any results...

[Gary R]

Looks fine except for a syntax thing.  Each portion of the aggregation pipeline is a document unto itself.
Try as aggregate( {$match...}, {$group...}, {$match} )

Gary R.

[ls]

Thanks a lot for your help. Meanwhile I got it. You're so nice.

kind regards

Ls