CODEWITHSUNDEEP
mysqlmongodbphp-mongodb
user1928870
user1928870

Reputation: 23

getting needed documents with limit

I am using Mongodb, but I can migrate to mysql, My problem is, I have a collection which consists datas like this one,

array(
"L"=>"cars",
"M"=>"wolkswagen"
)
array(
"L"=>"cars",
"M"=>"bmw"
)
array(
"L"=>"cars",
"M"=>"mercedes"
)


array(
"L"=>"bike",
"M"=>"bianchi"
)
array(
"L"=>"bike",
"M"=>"trek"
)
array(
"L"=>"bike",
"M"=>"brook"
)

What I want to get as a result, any bike and car, but 2 results for each of them, so the result should be,

array(
"L"=>"cars",
"M"=>"some car"
)
array(
"L"=>"cars",
"M"=>"some car"
)


array(
"L"=>"bike",
"M"=>"some bike"
)
array(
"L"=>"bike",
"M"=>"some bike"
)

I tried to do this with $in, but does not seem to work, any ideas ?

Upvotes: 0

Views: 73

Answers (1)

Stennie
Stennie

Reputation: 65313

There are a few ways to solve this problem using the Aggregation Framework in MongoDB 2.2+.

Unfortunately the Aggregation Framework as at 2.2.2 doesn't support a $slice or array subset operator yet, so some extra manipulation is required.

Note: The examples below are using the mongo shell, but should be straightforward to translate to the PHP driver.

Example #1: use Aggregation Framework to extract two matches (first & last)

Assuming you aren't fussed which two elements you get for each grouping, you could select the $first and $last:

db.cars.aggregate(

    // Group by $L and find the first and last elements
    { $group: {
        _id: '$L',
        first: { $first: "$M" },
        last: { $last: "$M" },
    }},

    // Add an extra $index for # of array elements
    { $project: {
        first: 1,
        last: 1,
        index: { $const:[0,1] }     
    }},

    // Split into document stream based on $index
    { $unwind: '$index' },

    // Re-group data using conditional to create array 
    { $group: {
        _id: '$_id',
        M: {
            $push: { $cond:[ {$eq:['$index', 0]}, '$first', '$last'] }
        }
    }}
)

Sample output:

{
    "result" : [
        {
            "_id" : "cars",
            "M" : [
                "wolkswagen",
                "mercedes"
            ]
        },
        {
            "_id" : "bike",
            "M" : [
                "bianchi",
                "brook"
            ]
        }
    ],
    "ok" : 1
}

Example #2: use Aggregation Framework to create a set, and then reduce

An alternative to the above would be a simpler pipeline with $addToSet and a post-processing step to trim the array elements:

var agg = db.cars.aggregate(
    { $group: {
        _id: '$L',
        'M': { $addToSet: "$M" },
    }}
)

// Iterate to trim each result to the desired # of elements
agg.result.forEach(function(doc) {
    // Note: you could randomly select 2 (or n) elements here
    doc.M = doc.M.slice(0,2);
})

The output format will be the same as with example #1, but instead of selecting the first & last elements, example #2 (as written) selects the first two elements.

Upvotes: 1

Related Questions