MongoDB join two collections having JSON array data

Question

I am able to write query for normal single key-value joining with $lookup. But my scenario is little bit complicated, and not sure how to achieve that.

product :

{
    "_id": ObjectId("6200a77598412e443c03f0ee"),
    "name": "1000",
    "sku_code": "22",
    "field_values": [{
        "field_id": ObjectId("61fd34cbbc787e45d256a270"),
        "key": "P4YZRK71CZAQ8IXJ3",
        "value": "54"
    }, {
        "field_id": ObjectId("61fb77b2384e2635f8a2d1ea"),
        "key": "SF_10KRUMSESSIONSPERMONTH",
        "value": "34"
    }]
}

field :

{
    "_id": ObjectId("61fb77b1384e2635f8a2d1e9"),
    "description": "sf_infrastructureFunction__c",
    "is_read_only": false,
    "is_required": false,
    "name": "sf_infrastructureFunction",
    "key": "SF_INFRASTRUCTUREFUNCTION",
    "type": "number"
}, {
    "_id": ObjectId("61fb77b2384e2635f8a2d1ea"),
    "default": "123",
    "description": "sf_10KRumSessionsPerMonth__c",
    "is_read_only": false,
    "is_required": false,
    "name": "sf_10KRumSessionsPerMonth",
    "key": "SF_10KRUMSESSIONSPERMONTH",
    "type": "number"
}, {
    "_id": ObjectId("61fd34cbbc787e45d256a270"),
    "description": "UKXNVYBF7AXE1VOUH",
    "is_read_only": false,
    "is_required": true,
    "name": "P4YZRK71CZAQ8IXJ3",
    "key": "P4YZRK71CZAQ8IXJ3",
    "type": "number"
}

Final expected output for product(6200a77598412e443c03f0ee) :

{
    "_id": ObjectId("61fb77b1384e2635f8a2d1e9"),
    "description": "sf_infrastructureFunction__c",
    "is_read_only": false,
    "is_required": false,
    "name": "sf_infrastructureFunction",
    "key": "SF_INFRASTRUCTUREFUNCTION",
    "type": "number",
}, {
    "_id": ObjectId("61fb77b2384e2635f8a2d1ea"),
    "default": "123",
    "description": "sf_10KRumSessionsPerMonth__c",
    "is_read_only": false,
    "is_required": false,
    "name": "sf_10KRumSessionsPerMonth",
    "key": "SF_10KRUMSESSIONSPERMONTH",
    "type": "number",
    "field_data" : {
        "key": "SF_10KRUMSESSIONSPERMONTH",
        "value": "34"
    }
}, {
    "_id": ObjectId("61fd34cbbc787e45d256a270"),
    "description": "UKXNVYBF7AXE1VOUH",
    "is_read_only": false,
    "is_required": true,
    "name": "P4YZRK71CZAQ8IXJ3",
    "key": "P4YZRK71CZAQ8IXJ3",
    "type": "number",
    "field_data" : {
        "key": "P4YZRK71CZAQ8IXJ3",
        "value": "54"
    }
}

Kindly note in first JSON in sample output, product has not stored any field_values for that field. Overall, I want all fields data with their values for specific product id, and if it does not have values, then plain configuration of field.

R2D2 · Accepted Answer · 2022-02-08 10:39:39Z

2

Something like this:

db.field.aggregate([
{
 "$lookup": {
  "from": "product",
  "localField": "_id",
  "foreignField": "field_values.field_id",
  "as": "field_data"
 }
 },
 {
   $unwind: {
    path: "$field_data",
    preserveNullAndEmptyArrays: true
 }
 },
 {
  "$addFields": {
  "field_data": {
    "$filter": {
      "input": "$field_data.field_values",
      "as": "fv",
      "cond": {
        $eq: [
          "$$fv.field_id",
          "$_id"
        ]
       }
      }
     }
    }
   },
   {
    $addFields: {
     field_data: {
      $cond: {
       if: {
        $eq: [
          "$field_data",
          null
        ]
       },
       then: "$$REMOVE",
       else: "$field_data"
      }
     }
    }
   }
 ])

Explained:

$lookup to add the product to the field documents
$unwind to flatten the resulting array ( it contain product document ) preserving the null elements
$filter to keep only the matching products.
Remove the null field_data for those products that is null.

playground

edited Feb 8, 2022 at 10:39

answered Feb 7, 2022 at 19:45

R2D2

10.9k2 gold badges20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Darshan Patel Over a year ago

Thanks, this is really close to what I need. Only remaining tricky part is, it is missing the field where there is no data. So, expected result is 3 fields.

R2D2 Over a year ago

Ah just saw the missed points and correct , please, check now is exactly as expected

Collectives™ on Stack Overflow

MongoDB join two collections having JSON array data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related