MongoDB Inner Array Query 4.0

Hello,

I have MongoDB document like this. I need to get all data where “SID”: 100.
The output should have similar format as input.
Currently I am using MongoDB 4.0. How do I achieve this.

Input

[
  {
    "_id": "123456",
    "Continent": {
      "Country": [
        [
          "US",
          {
            "State": [
              [
                100,
                {
                  "Product": "Corn",
                  "SID": 100
                }
              ],
              [
                200,
                {
                  "Product": "Maze",
                  "SID": 200
                }
              ],
              [
                100,
                {
                  "Product": "Corn-HB",
                  "SID": 100
                }
              ]
            ],
            
          }
        ]
      ]
    }
  }
]

Expected Output

[
  {
    "_id": "123456",
    "Continent": {
      "Country": [
        [
          "US",
          {
            "State": [
              [
                100,
                {
                  "Product": "Corn",
                  "SID": 100
                }
              ],
              [
                100,
                {
                  "Product": "Corn-HB",
                  "SID": 100
                }
              ]
            ],
            
          }
        ]
      ]
    }
  }
]

Thanks

Hello :wave: @Sym_Don,

Welcome to the MongoDB Community forums :sparkles:

The input schema seems complex and it includes Array of objects within an Array of array of objects, however I tested the following aggregation pipeline approach:

db.collections.aggregate([
    {
        "$unwind": "$Continent.Country"
    },
    {
        "$addFields": {
            State: {
                "$let": {
                    vars: { idx1: { "$arrayElemAt": ["$Continent.Country", 1] } },
                    in: {
                        "$filter": {
                            input: "$$idx1.State",
                            cond: {
                                "$eq": ["$$this.SID", [100]]
                            }
                        }
                    }
                }
            }
        }
    },
    {
        "$set": {
            "Continent.Country": {
                "$map": {
                    input: {
                        "$range": [0, { "$size": "$Continent.Country" }]
                    },
                    in: {
                        "$cond": [{ "$eq": ["$$this", 1] },
                        { State: "$State" },
                        { "$arrayElemAt": ["$Continent.Country", "$$this"] }]
                    }
                }
            }
        }
    },
    {
        "$project": { State: 0 }
    }
])

Here, the example code assumes that the object containing the "State" array is always in array index position 1 within the unwinded "Continent.Country" array.

However, below is the explanation of the query.

  • The $unwind operator deconstructs an array field from the input documents to output a document for each element.

  • The $addFields operator adds new fields to the documents

  • Here idx1 is the variable that stores the element at index 1 of "Continent.Country", and then performed further operations on it.

  • The $filter operator returns an array with only those elements that match the condition

  • The $set operator replaces the value of a field with the specified value.

  • The $arrayElemAt returns the element at the specified array index.

  • The $map applies an expression to each item in an array and returns an array with the applied results.

which result the following output:

[
  {
    _id: '123456',
    Continent: {
      Country: [
        'US',
        {
          State: [
            [ 100, { Product: 'Corn', SID: 100 } ],
            [ 100, { Product: 'Corn-HB', SID: 100 } ]
          ]
        }
      ]
    }
  }
]

This is an un-tested code, so please test thoroughly in a test environment to verify it suits all your use case(s) and requirements.

I will, however, recommend that you alter the schema to make it easier to use and scale. Otherwise, it won’t scale well.

Q: Also, is it a one-off or are you planning to use it very frequently?

Thanks,
Kushagra

3 Likes

This is one-off collection which have this schema. Thanks for the solution.

1 Like

I made a small addition brackets ‘[’ at “Continent.Country”: [, and also closing bracket. to exactly match my output.

db.collection.aggregate([
  {
    "$unwind": "$Continent.Country"
  },
  {
    "$addFields": {
      State: {
        "$let": {
          vars: {
            idx1: {
              "$arrayElemAt": [
                "$Continent.Country",
                1
              ]
            }
          },
          in: {
            "$filter": {
              input: "$$idx1.State",
              cond: {
                "$eq": [
                  "$$this.SID",
                  [
                    100
                  ]
                ]
              }
            }
          }
        }
      }
    }
  },
  {
    "$set": {
      "Continent.Country": [
        {
          "$map": {
            input: {
              "$range": [
                0,
                {
                  "$size": "$Continent.Country"
                }
              ]
            },
            in: {
              "$cond": [
                {
                  "$eq": [
                    "$$this",
                    1
                  ]
                },
                {
                  State: "$State"
                },
                {
                  "$arrayElemAt": [
                    "$Continent.Country",
                    "$$this"
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    "$project": {
      State: 0
    }
  }
])
1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.