Mongodb aggregation nested query

Question

Sorry about the bad title, but here is the problem I'm trying to solve:

I have a couple of collections, including ticket, fields, and fieldOptions that look something like this:

Ticket:

{
    _id: 1,
    subject: "Lorem ipsum dolor sit amet, consectetur adipiscing elit",
    fields: [
        {
            key: "part-number",
            value: "abc123",
        },
        {
            key: "price",
            value: "10",
        },
        {
            key: "officer",
            value: "2",
        },
    ]
}

Fields:

[
    {
        _id: 1,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        options: [],
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        displayOrder: 0,
        key: "part-number",
        label: "Part Number",
        required: false,
        type: "text",
    },
    {
        _id: 2,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        options: [],
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        displayOrder: 1,
        key: "price",
        label: "Price",
        required: false,
        type: "text",
    },
    {
        _id: 3,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        options: [1, 2, 3, 4],
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        displayOrder: 2,
        key: "officer",
        label: "Officer",
        required: false,
        type: "select",
    },
    // THIS WAS ADDED AFTER THE 'TICKET' DOC WAS CREATED, SO IT'S NOT LISTED UNDER 'FIELDS' IN THERE
    {
        _id: 4,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        options: [],
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        displayOrder: 1,
        key: "notes",
        label: "Notes",
        required: false,
        type: "text",
    },
]

Field Options:

[
    {
        _id: 1,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        key: "none",
        label: "None",
        displayOrder: 0,
        legacy: false,
    },
    {
        _id: 2,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        key: "picard",
        label: "Picard",
        displayOrder: 1,
        legacy: false,
    },
    {
        _id: 3,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        key: "riker",
        label: "Riker",
        displayOrder: 2,
        legacy: false,
    },
    {
        _id: 4,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        key: "data",
        label: "Data",
        displayOrder: 3,
        legacy: false,
    },
    {
        _id: 5,
        created: ISODate("2020-09-10T20:23:46.382Z"),
        updated: ISODate("2020-09-10T20:23:46.382Z"),
        active: true,
        key: "red-shirt",
        label: "Red Shirt",
        displayOrder: 4,
        legacy: true,
    },
]

In my aggregation pipeline, I have the following:

[
    { $match: {_id: 2} },
    { $unwind: { path: "$fields", "preserveNullAndEmptyArrays": true } },
            {
                "$lookup": {
                    "from": "fields",
                    "let": { "key": "$fields.key" },
                    "as": "fields.def",
                    "pipeline": [
                        { "$match": { "$expr": { "$eq": ["$key", "$$key"] } } },
                        {
                            "$lookup": {
                                "from": "fieldoptions",
                                "let": { "options": "$options" },
                                "pipeline": [
                                    { "$match": { "$expr": { "$in": ["$_id", "$$options"] } } },
                                ],
                                "as": "options"
                            }
                        }
                    ]
                }
            },
            { $unwind: { path: "$fields.def", "preserveNullAndEmptyArrays": true } },
            {
                $group: {
                    _id: "$_id",
                    fields: { $push: "$$ROOT.fields" },
                    root: { $mergeObjects: "$$ROOT" },
                }
            },
            {
                $replaceRoot: {
                    newRoot: {
                        $mergeObjects: ["$root", "$$ROOT"]
                    }
                }
            },
            {
                $unset: "root"
            },
            { $project: { fields: '$fields'}} // this is to remove other junk for testing
]

Which brings back the following (almost correct) result:

{
    "_id" : 2,
    "fields" : [ 
        {
            "key" : "part-number",
            "value" : "abc123",
            "def" : {
                "_id" : 1,
                "created" : ISODate("2020-09-10T20:23:46.382Z"),
                "options" : [],
                "updated" : ISODate("2020-09-10T20:23:46.382Z"),
                "active" : true,
                "displayOrder" : 0,
                "key" : "part-number",
                "label" : "Part Number",
                "required" : false,
                "type" : "text",
                "__v" : 0
            }
        }, 
        {
            "key" : "price",
            "value" : "10",
            "def" : {
                "_id" : 2,
                "created" : ISODate("2020-09-10T20:23:46.382Z"),
                "options" : [],
                "updated" : ISODate("2020-09-10T20:23:46.382Z"),
                "active" : true,
                "displayOrder" : 1,
                "key" : "price",
                "label" : "Price",
                "required" : false,
                "type" : "text",
                "__v" : 0
            }
        }, 
        {
            "key" : "officer",
            "value" : "2",
            "def" : {
                "_id" : 3,
                "created" : ISODate("2020-09-10T20:23:46.382Z"),
                "options" : [ 
                    {
                        "_id" : 1,
                        "created" : ISODate("2020-09-10T20:23:46.382Z"),
                        "updated" : ISODate("2020-09-10T20:23:46.382Z"),
                        "active" : true,
                        "key" : "none",
                        "label" : "None",
                        "displayOrder" : 0,
                        "legacy" : false,
                        "__v" : 0
                    }, 
                    {
                        "_id" : 2,
                        "created" : ISODate("2020-09-10T20:23:46.382Z"),
                        "updated" : ISODate("2020-09-10T20:23:46.382Z"),
                        "active" : true,
                        "key" : "picard",
                        "label" : "Picard",
                        "displayOrder" : 1,
                        "legacy" : false,
                        "__v" : 0
                    }, 
                    {
                        "_id" : 3,
                        "created" : ISODate("2020-09-10T20:23:46.382Z"),
                        "updated" : ISODate("2020-09-10T20:23:46.382Z"),
                        "active" : true,
                        "key" : "riker",
                        "label" : "Riker",
                        "displayOrder" : 2,
                        "legacy" : false,
                        "__v" : 0
                    }, 
                    {
                        "_id" : 4,
                        "created" : ISODate("2020-09-10T20:23:46.382Z"),
                        "updated" : ISODate("2020-09-10T20:23:46.382Z"),
                        "active" : true,
                        "key" : "data",
                        "label" : "Data",
                        "displayOrder" : 3,
                        "legacy" : false,
                        "__v" : 0
                    }
                ],
                "updated" : ISODate("2020-09-10T20:23:46.382Z"),
                "active" : true,
                "displayOrder" : 2,
                "key" : "officer",
                "label" : "Officer",
                "required" : false,
                "type" : "select",
                "__v" : 0
            }
        }
    ]
}

My issue is that if I add something to the 'fields' collection (let's call it property 'foo'), the existing ticket objects do not have the 'foo' ref, so it does not show up in the ticket object, I would like to somehow (in the aggregation framework) get the list of current fields, and for each look up the corresponding value if it exists, otherwise return with an empty value like so:

new ticket result:

{
    _id: 2,
    fields: [
        ... //existing fields
        {
            key: "foo", // <-- new fields / fields not listed on the ticket
            value: null,
            def: {
                _id: 1,
                created: ISODate("2020-09-10T20:23:46.382Z"),
                options: [],
                updated: ISODate("2020-09-10T20:23:46.382Z"),
                active: true,
                displayOrder: 0,
                key: "foo",
                label: "Foo",
                required: false,
                type: "text",
                __v: 0,
            },
        },
    ],
}

How can this be accomplished? Also, is there a better way to do this than what I have? I'm very new to aggregation and still experimenting.

Is there something in the provided answer that you believe does not address your question? If so then please comment on the answer to clarify what exactly needs to be addressed that has not. — turivishal
– turivishal, Commented Nov 3, 2020 at 18:03

turivishal · Accepted Answer · 2020-10-11 18:28:17Z

Its is totally lengthy process to combine both fields, you can follow the steps,

$match your condition
$addFields to add a array of fields key in a keys array
$facet to generate 2 separate array, one for matched fields and second for unmatched array
- make array of matched fields in fields, pass keys array in let and match $in query and lookup with fieldoptions collection,
  - $project to get combine objects with current root keys and value using $map and $reduce
- make array of unmatched fields in other_fields, pass keys array in let and match $not and $in query means not include and lookup with fieldoptions collection
  - $project to get combine objects with current root keys and value using $map and $mergeObjects
$project to concat both arrays in fields array using $concatArrays
$unwind deconstruct fields array
$replaceWith fields object replace to root
$unwind deconstruct fields array
$group by ticket id and construct array of fields

db.ticket.aggregate([
  { $match: { _id: 1 } },
  { $addFields: { keys: { $map: { input: "$fields", in: "$$this.key" } } } },
  {
    $facet: {
      fields: [
        {
          $lookup: {
            from: "fields",
            let: { key: "$keys" },
            as: "_fields",
            pipeline: [
              { $match: { $expr: { $in: ["$key", "$$key"] } } },
              {
                $lookup: {
                  from: "fieldoptions",
                  localField: "options",
                  foreignField: "_id",
                  as: "options"
                }
              }
            ]
          }
        },
        {
          $project: {
            fields: {
              $map: {
                input: "$fields",
                as: "f",
                in: {
                  $mergeObjects: [
                    "$$f",
                    {
                      def: {
                        $reduce: {
                          input: "$_fields",
                          initialValue: {},
                          in: {
                            $cond: [{ $eq: ["$$f.key", "$$this.key"] }, "$$this", "$$value"]
                          }
                        }
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      ],
      other_fields: [
        {
          $lookup: {
            from: "fields",
            let: { key: "$keys" },
            as: "_fields",
            pipeline: [
              { $match: { $expr: { $not: { $in: ["$key", "$$key"] } } } },
              {
                $lookup: {
                  from: "fieldoptions",
                  localField: "options",
                  foreignField: "_id",
                  as: "options"
                }
              }
            ]
          }
        },
        {
          $project: {
            fields: {
              $map: {
                input: "$_fields",
                as: "f",
                in: {
                  $mergeObjects: [
                    { def: "$$f" },
                    { key: "$$f.key", value: null }
                  ]
                }
              }
            }
          }
        }
      ]
    }
  },
  { $project: { fields: { $concatArrays: ["$fields", "$other_fields"] } } },
  { $unwind: "$fields" },
  { $replaceWith: "$fields" },
  {
    $unwind: {
      path: "$fields",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: "$_id",
      fields: { $push: "$$ROOT.fields" }
    }
  }
])

Playground

Your query in question, this is a optimized version of that query Playground

Collectives™ on Stack Overflow

Mongodb aggregation nested query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related