here I have an sql query which needs to converted to mongo query. sql query:

select p.producer_id,p.name from producer p join
(select distinct ps.service_id,ps.value from service ps where ps.service_id=p.service_id) join
(select distinct pp.property_id,pp.property from property pp where  pp.service_id=p.service_id) 

My mongo query:

db.producer.aggregate([
        {
          "$lookup": {
            "from": "service",
            "localField": "producer_id",
            "foreignField": "service_id",
            "as": "ps"
          }
        },
        {
          "$unwind": "$ps"
        },
        {
          "$lookup": {
            "from": "property",
            "localField": "producer_id,
            "foreignField": "property_id",
            "as": "pp"
          }
        },
        {
          "$unwind": "$pp"
        },
        {
          "$group": {
            "_id": {
              "property_id": "$pp.property_id",
              "service_id": "$ps.service_id"
            }
          }
        },
        {
          "$project": {
            "producer_id": "$p.producer_id",
            "name": "$p.name",
            "service_id":"$ps.service_id",
            "value":"$ps.value",
            "property_id":"$pp.property_id",
            "property":"$pp.property",
            "_id":0
          }
        }

      ]);

sample input records: producer:

[{producer_id:1,name:'test'},{producer_id:2,name:'test2'}]

service:

[{service_id:1,value:12},{service_id:1,value:13},{service_id:2,value:14}]

property:

[{property_id:1,property:12},{property_id:1,property:56},{property_id:2,property:34}]

But in output, I am able to see group result. When I trying to trying to project the remaining columns ('name','value','property' fields) from respective collections those values are not displaying in the output. Here I have to select distinct records and display distinct records along with other records. When I try to add all fields in group like ('name','value','property' fields) I am able to see all records but the performance is slow. Can anyone please help me on this?

CodePudding user response：

In your query, in the $group stage, you need to use accumulator operator for your extra fields

{
    "$group": {
        "_id": {
            "property_id": "$pp.property_id",
            "service_id": "$ps.service_id"
        },
        "field": {
          "accumulator": "$value"
        },
        ...
    }
}

Accumulator Operator

Try this one:

db.producer.aggregate([
  //make sure service.service_id is indexed for better performance
  {
    "$lookup": {
      "from": "service",
      "localField": "producer_id",
      "foreignField": "service_id",
      "as": "ps"
    }
  },
  //make sure property.property_id is indexed for better performance
  {
    "$lookup": {
      "from": "property",
      "localField": "producer_id",
      "foreignField": "property_id",
      "as": "pp"
    }
  },
  {
    "$unwind": "$pp"
  },
  {
    "$unwind": "$ps"
  },
  {
    "$group": {
      // SQL "distinct"
      "_id": {
        "service_id": "$ps.service_id",
        "property_id": "$pp.property_id",
        "value": "$ps.value",
        "property": "$pp.property"
      },
      "producer_id": {
        "$first": "$producer_id"
      },
      "name": {
        "$first": "$name"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "producer_id": "$producer_id",
      "name": "$name",
      "service_id": "$_id.service_id",
      "property_id": "$_id.property_id",
      "value": "$_id.value",
      "property": "$_id.property"
    }
  }
])

MongoPlayground