'MongoDB join two collections having JSON array data

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'MongoDB join two collections having JSON array data

Sources

Related Questions