聚合查询，多表统计数据的demo

个人也是小白，想实现多表统计功能，一直看不懂官方文档，今天找了大佬写了一个demo，有需要的可以了解一下。

我想实现的效果

user表
[
{
"_id": "64c22b3e189f866d65c3d0eb",
"name": "张三",
"sex": 1
},
{
"_id": "64c22b5a337a9f4db7882114",
"name": "王五",
"sex": 1
},
{
"_id": "64c22b5055b337825753d977",
"name": "李娇",
"sex": 0
},
{
"_id": "64c22b6bbd0220bf8c7051c5",
"name": "六花",
"sex": 0
}
]

sp表
[
{
"_id": "64c22cbe99c6246543bbfe9d",
"je": 20,
"name_id": "64c22b5055b337825753d977",
"sp": "李娇的商品1"
},
{
"_id": "64c22ce36e5d2d8f64af3f8e",
"je": 20,
"name_id": "64c22b5a337a9f4db7882114",
"sp": "王五的商品4"
},
{
"_id": "64c22cd97ad52ddc6482f40a",
"je": 20,
"name_id": "64c22b5a337a9f4db7882114",
"sp": "王五的商品2"
},
{
"_id": "64c22c766e5d2d8f64af2804",
"je": 20,
"name_id": "64c22b3e189f866d65c3d0eb",
"sp": "张三的商品2"
},
{
"_id": "64c22cf7f08210d515b35a86",
"je": 20,
"name_id": "64c22b6bbd0220bf8c7051c5",
"sp": "六花的商品1"
},
{
"_id": "64c22cd421821b2af5b6b48a",
"je": 20,
"name_id": "64c22b5a337a9f4db7882114",
"sp": "王五的商品1"
},
{
"_id": "64c22cdf337a9f4db78875c8",
"je": 20,
"name_id": "64c22b5a337a9f4db7882114",
"sp": "王五的商品3"
},
{
"_id": "64c22c71e0ec19bea1b13e15",
"je": 20,
"name_id": "64c22b3e189f866d65c3d0eb",
"sp": "张三的商品1"
},
{
"_id": "64c22cc27ad52ddc6482eecb",
"je": 20,
"name_id": "64c22b5055b337825753d977",
"sp": "李娇的商品2"
},
{
"_id": "64c22c7aa09a9bd68ba01823",
"je": 20,
"name_id": "64c22b3e189f866d65c3d0eb",
"sp": "张三的商品3"
}
]

wz表
[
{
"_id": "64c22f43337a9f4db788fdd8",
"name_id": "64c22b3e189f866d65c3d0eb",
"wzdz": 12,
"wzname": "六花的文章2"
},
{
"_id": "64c22f5ffe975fba5a5e51ef",
"name_id": "64c22b5055b337825753d977",
"wzdz": 34,
"wzname": "李娇的文章2"
},
{
"_id": "64c22f57337a9f4db7890253",
"name_id": "64c22b5055b337825753d977",
"wzdz": 3,
"wzname": "李娇的文章1"
},
{
"_id": "64c22f3ef08210d515b3e212",
"name_id": "64c22b3e189f866d65c3d0eb",
"wzdz": 12,
"wzname": "六花的文章1"
},
{
"_id": "64c22f7b6e5d2d8f64afdba0",
"name_id": "64c22b5a337a9f4db7882114",
"wzdz": 54,
"wzname": "王五的文章2"
},
{
"_id": "64c22f739755e344ab9c51ce",
"name_id": "64c22b5a337a9f4db7882114",
"wzdz": 11,
"wzname": "王五的文章1"
},
{
"_id": "64c235cd466d416d30844f4e",
"name_id": "64c22b5a337a9f4db7882114",
"wzdz": 13,
"wzname": "王五的文章3"
}
]

最终结果

张三 商品3 点赞24
李 商品2 点赞37
王五 商品4 点赞65
六花 商品1 点赞0

聚合查询代码

const db = uniCloud.database()  
const dbCmd = db.command  
const $ = dbCmd.aggregate  

exports.main = async (event, context) => {  

    const {data}=await db.collection(`user`).aggregate()  
    .lookup({  
        from:`sp`,  
        let:{  
            user_id:`$_id`,  
        },  
        pipeline:$.pipeline()  
        .match(  
            dbCmd.expr(  
                $.eq(['$name_id','$$user_id'])  
            )  
        )  
        .group({  
            _id:'$name_id',  
            count: $.sum(1),  
        })  
        .done(),  
        as: 'sp_list',  
    })  
    .lookup({  
        from:`wz`,  
        let:{  
            user_id:`$_id`,  
        },  
        pipeline:$.pipeline()  
        .match(  
            dbCmd.expr(  
                $.eq(['$name_id','$$user_id'])  
            )  
        )  
        .group({  
            _id:'$name_id',  
            wzdz:$.sum('$wzdz'),  
        })  
        .done(),  
        as: 'wz_list',  
    })  
    .end();  

    return {  
        data:data  
    }  
};