Migrar de um RDBMS para o MongoDB com a ajuda da AI: uma introdução ao conversor de query

1
SELECT contact_name, country 
2
FROM customers 
3
WHERE country = 'USA'

1
async function query(db) {
2
    return await db.collection('customers').find({ country: 'USA' }, { projection: { contactName: 1, country: 1, _id: 0 } }).toArray();
3
}

1
SELECT country, COUNT(*) AS total
2
FROM customers 
3
GROUP BY country

1
async function query(db) {
2
    return await db.collection('customers').aggregate([
3
        { $group: { _id: "$country", total: { $sum: 1 } } }
4
    ]).toArray();
5
}

1
SELECT customers.contact_name, SUM(order_details.unit_price) AS spent 
2
FROM orders
3
JOIN customers ON orders.customer_id = customers.customer_id
4
JOIN order_details ON orders.order_id = order_details.order_id
5
JOIN products ON order_details.product_id = products.product_id
6
GROUP BY customers.contact_name

1
async function query(db) {
2
    return await db.collection('orders').aggregate([
3
        {
4
            $lookup: {
5
                from: 'customers',
6
                localField: 'customerId',
7
                foreignField: 'customerId',
8
                as: 'customer'
9
            }
10
        },
11
        {
12
            $unwind: '$customer'
13
        },
14
        {
15
            $lookup: {
16
                from: 'orderDetails',
17
                localField: 'orderId',
18
                foreignField: 'orderId',
19
                as: 'orderDetail'
20
            }
21
        },
22
        {
23
            $unwind: '$orderDetail'
24
        },
25
        {
26
            $lookup: {
27
                from: 'products',
28
                localField: 'orderDetail.productId',
29
                foreignField: 'productId',
30
                as: 'product'
31
            }
32
        },
33
        {
34
            $unwind: '$product'
35
        },
36
        {
37
            $group: {
38
                _id: '$customer.contactName',
39
                spent: {
40
                    $sum: '$orderDetail.unitPrice'
41
                }
42
            }
43
        },
44
        {
45
            $project: {
46
                _id: 0,
47
                contactName: '$_id',
48
                spent: 1
49
            }
50
        }
51
    ]).toArray();
52
}