how to query mongodb like union of sql

Question

I have resloved this question:

db.coll1.aggregate([
    {
        $match:{
            $or:[
                {time:{$lt:145941000},code:413}
                ,{time:{$lt:145942000},code:415}
            ]
        }
    }   
    ,{  $sort:{time:-1}}
    ,{  $group:{_id:"$code"
            ,lastMatch:{$first:"$price"}
        }
    }
])

This is the collection below:

collection

I want to query like this:

SELECT code, price FROM table1 WHERE code = 1 AND time<123xxx ORDER BY time desc LIMIT 1
UNION
SELECT code, price FROM table1 WHERE code = 2 AND time<24xxx ORDER BY time desc LIMIT 1
UNION
SELECT code, price FROM table1 WHERE code = 3 AND time<1xxx ORDER BY time desc LIMIT 1
UNION
...

How can I query mongodb like this? I know how query one but it seem that the aggregate of mongodb is not able to 'union'

db.m20170705.aggregate([
    {
        $match:{code:1
            ,time:{$lte:145940500}
        }
    }
    ,{$sort:{time:-1}}
    ,{$limit:1}
])
union ????

almost-duplicate of: https://stackoverflow.com/questions/4715820/how-to-order-by-with-union — Daniel Alder, Jul 10 '17 at 09:37

Daniel Alder · Answer 1 · 2017-07-10T09:34:20.147

Without knowing mongodb (but knowing SQL), I would say ORDER BY is not part of the SELECTs in a UNION and thus has to be placed once at the end.

SELECT code, price FROM table1 WHERE code = 1 AND time<123xxx
UNION
SELECT code, price FROM table1 WHERE code = 2 AND time<24xxx
UNION
SELECT code, price FROM table1 WHERE code = 3 AND time<1xxx
UNION
...
ORDER BY time desc

(LIMIT works differently in this case, it has to be implemented otherwise, for example inside a subselect, if available in MongoDB)

score 0 · Answer 2 · answered Jul 10 '17 at 14:53

You can refer this: https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/

Sample:

table1.aggregate([
        {
            "$match":
                {
                    "$code": 2
                }
        },
        {
            "$project":
                {
                   "$code": 1,
                   "$price": 1
                }
        },
        {
            "$limit": 1
        }
])

how to query mongodb like union of sql

2 Answers2