CODEWITHSUNDEEP
node.jsmongodbmongodb-query
Suraj Dalvi
Suraj Dalvi

Reputation: 1078

Mongo Query to get a result

My mongo collection name tests and whose having the following documents in it.

[
  {
    "title": "One",
    "uid": "1",
    "_metadata": {
      "references": [
        {
          "uid": "2"
        },
        {
          "asssetuid": 10
        }
      ]
    }
  },
  {
    "title": "Two",
    "uid": "2",
    "_metadata": {
      "references": [
        {
          "uid": "3"
        },
        {
          "asssetuid": 11
        }
      ]
    }
  },
  {
    "title": "Three",
    "uid": "3",
    "_metadata": {
      "references": []
    }
  }
]

And I want the result in the following format (for uid:1)

[
  {
    "title": "One",
    "uid": 1,
    "_metadata": {
      "references": [
        {
          "asssetuid": 10
        },
        {
          "asssetuid": 11
        },
        {
          "title": "Two",
          "uid": "2",
          "_metadata": {
            "references": [
              {
                "title": "Three",
                "uid": "3"
              }
            ]
          }
        }
      ]
    }
  }
]

for uid:2 I want the following result

[
  {
    "title": "Two",
    "uid": 2,
    "_metadata": {
      "references": [
        {
          "asssetuid": 11
        },
        {
          "title": "Three",
          "uid": "3"
        }
      ]
    }
  }
]

Which query I used here to get a respected result. according to its uid. here I want the result in the parent-child relationship. is this possible using MongoDB graph lookup query or any other query that we can use to get the result. Please help me with this.

New Type Output

[{
"title": "One",
"uid": 1,
"_metadata": {
    "assets": [{
        "asssetuid": 10,
        "parent": 1
    }, {
        "asssetuid": 11,
        "parent": 2
    }],
    "entries": [{
        "title": "Two",
        "uid": "2",
        "parent": 1
    }, {
        "title": "Three",
        "uid": "3",
        "parent": 2
    }]
}

}]

Upvotes: 0

Views: 74

Answers (1)

HandsomeCoder
HandsomeCoder

Reputation: 756

Mongo supports the automatic reference resolution using $ref but for that, you need to change your schema a little and resolve resolution is only supported by some drivers.

You need to store your data in this format:

[
  ...
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "_metadata": {
      "references": [
        {
          "$ref": "collection",
          "$id": ObjectId("5a934e000102030405000001"),
          "$db": "database"
        },
        {
          "asssetuid": 10
        }
      ]
    },
    "title": "One",
    "uid": "1"
  },
  ....
]

For more details on $ref refer to official documentation: label-document-references

OR

you can resolve the reference using the $graphLookup but the only problem with the $graphlookup is that you will lose the assetuid. Here is the query and it will resolve references and give output in flat map

db.collection.aggregate([
  {
    $match: {
      uid: "1"
    }
  },
  {
    $graphLookup: {
      from: "collection",
      startWith: "$_metadata.references.uid",
      connectFromField: "_metadata.references.uid",
      connectToField: "uid",
      depthField: "depth",
      as: "resolved"
    }
  },
  {
    "$addFields": {
      "references": "$resolved",
      "metadata": [
        {
          "_metadata": "$_metadata"
        }
      ]
    }
  },
  {
    "$project": {
      "references._metadata": 0,
      
    }
  },
  {
    "$project": {
      "references": "$references",
      "merged": {
        "$concatArrays": [
          "$metadata",
          "$resolved"
        ]
      }
    }
  },
  {
    "$project": {
      results: [
        {
          merged: "$merged"
        },
        {
          references: "$references"
        }
      ]
    }
  },
  {
    "$unwind": "$results"
  },
  {
    "$facet": {
      "assest": [
        {
          "$match": {
            "results.merged": {
              "$exists": true
            }
          }
        },
        {
          "$unwind": "$results.merged"
        },
        {
          "$unwind": "$results.merged._metadata.references"
        },
        {
          "$match": {
            "results.merged._metadata.references.asssetuid": {
              "$exists": true
            }
          }
        },
        {
          "$project": {
            _id: 0,
            "asssetuid": "$results.merged._metadata.references.asssetuid"
          }
        }
      ],
      "uid": [
        {
          "$match": {
            "results.references": {
              "$exists": true
            }
          }
        },
        {
          "$unwind": "$results.references"
        },
        {
          $replaceRoot: {
            newRoot: "$results.references"
          }
        }
      ]
    }
  },
  {
    "$project": {
      "references": {
        "$concatArrays": [
          "$assest",
          "$uid"
        ]
      }
    }
  }
])

Here is the link to the playground to test it: Mongo Playground

Upvotes: 1

Related Questions