mongo关联查询时筛选从表的条件

db.主表名.aggregate([{
        $lookup:{
            from:"从表名",
            localField:"主键",
            foreignField:"外键",
            as:"asField" //查询结果中外键集合对应集合名,与sql的as类似
        }
    },
    {$match:{"主表字段":"条件","asField.从表字段":"条件"} }])

注意match要在lookup后,从表筛选条件需要用lookup中as关键字所取用的名称+从表字段名选取

db.adv.aggregate([{
    $lookup:{
        from:"company",
        localField:"name",
        foreignField:"companyName",
        as:"addr"
   	 }
  },
  {$match:{"address":"北京","addr.address_province":"北京","addr.address_city":"北京市","addr.address_province":"海淀区"}}])

mongoTemplate中使用:

import java.util.List;
import com.mongodb.BasicDBObject;
import org.springframework.stereotype.Repository;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.LimitOperation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;

public class AdvertisementDaoImpl implements AdvertisementDao {
    @Autowired
    private MongoTemplate mongoTemplate;
    @Override
    public List advCount(String prov, String city, String cityArea) {
        /**
         * db.final_adv_all.aggregate([{
         *     $lookup:{
         *         from:"companyFinal",
         *         localField:"e_nameOfAdvertiser",
         *         foreignField:"company",
         *         as:"addr"
         *     }
         * },{$match:{"e_address":"","addr.address_province":"","addr.address_city":""}}])
         */
        AggregationOperation match = null;
        Criteria criteria = new Criteria();
        Criteria criteriaEaddress = new Criteria();
        Criteria criteriaProv = new Criteria();
        Criteria criteriaCity = new Criteria();
        Criteria criteriaCityArea = new Criteria();
        LookupOperation lookup = LookupOperation.newLookup().
                from("companyFinal").
                localField("e_nameOfAdvertiser").
                foreignField("company").
                as("addr");
        criteriaEaddress = Criteria.where("e_address").is(prov);
        criteriaProv = Criteria.where("addr.address_province").is(prov);
        criteriaCity = Criteria.where("addr.address_city").is(city);
        if (!"".equals(cityArea)){
            criteriaCityArea = Criteria.where("addr.address_province").is(cityArea);
            criteria.andOperator(criteriaCityArea);
        }
        criteria.andOperator(criteriaEaddress,criteriaProv,criteriaCity);
        match = Aggregation.match(criteria);
//        LimitOperation limitOperation = Aggregation.limit(15);
        Aggregation aggregation = Aggregation.newAggregation(lookup, match);
//        Aggregation aggregation = Aggregation.newAggregation(match, lookup); 和上一行代码的差异,谁在前先执行谁;match在前,查询无结果!!!
        List resultList = this.mongoTemplate.aggregate(aggregation, "final_adv_all", BasicDBObject.class).getMappedResults();
        return resultList;
    }
}

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值