How to find data in array of object in mongodb that have different order?

Question

I have data in collection in mongodb like this :

[
{
    _id  : "ObjectId(1)",
    name : "data1",
    data : [
        { text: "a", value: 1 },
        { text: "b", value: 2 }
    ]
},
{
    _id  : "ObjectId(2)",
    name : "data2",
    data : [
        { text: "b", value: 2 },
        { text: "a", value: 1 }
    ]
}
]

like you see, data1 and data2 have same data but different order.

I need to find in this db.somecol.find({}) that return all document with same data that ignore order in array.

how to do that?

I try using $elemMatch but it can't.

that return all document with same data - a bit confused by this statement, So do you've any inputs to match with or do we need to scan all documents in collection to get documents which has matching objects in data array, I guess mostly it's with inputs { text: "b", value: 2 }, { text: "a", value: 1 }, can you just confirm - Also if it's the case do you need all inputs to exist in data array or any one object ? — whoami - fakeFaceTrueSoul
– whoami - fakeFaceTrueSoul, Commented Jan 22, 2020 at 17:25

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-01-22 18:07:07Z

You can try $all operator which will check all inputs exists in array irrespective of order.

db.somecol.find({
    data: {
        $all: [{
            "text": "a",
            "value": 1.0
        },
        {
            "text": "b",
            "value": 2.0
        }]
    }
})

Collection Data :

/* 1 */
{
    "_id" : "ObjectId(1)",
    "name" : "data1",
    "data" : [ 
        {
            "text" : "a",
            "value" : 1.0
        }, 
        {
            "text" : "b",
            "value" : 2.0
        }
    ]
}

/* 2 */
{
    "_id" : "ObjectId(2)",
    "name" : "data2",
    "data" : [ 
        {
            "text" : "b",
            "value" : 2.0
        }, 
        {
            "text" : "a",
            "value" : 1.0
        }
    ]
}

/* 3 */ /** This will not be returned in result */
{
    "_id" : "ObjectId(3)",
    "name" : "data1",
    "data" : [ 
        {
            "text" : "a",
            "value" : 3.0
        }, 
        {
            "text" : "b",
            "value" : 2.0
        }
    ]
}

Result :

/* 1 */
{
    "_id" : "ObjectId(1)",
    "name" : "data1",
    "data" : [ 
        {
            "text" : "a",
            "value" : 1.0
        }, 
        {
            "text" : "b",
            "value" : 2.0
        }
    ]
}

/* 2 */
{
    "_id" : "ObjectId(2)",
    "name" : "data2",
    "data" : [ 
        {
            "text" : "b",
            "value" : 2.0
        }, 
        {
            "text" : "a",
            "value" : 1.0
        }
    ]
}

Valijon · Accepted Answer · 2020-01-22 23:44:14Z

To scan whole collection to get documents which has matching objects in data array:

Explanation

I assume _id is unique and ordered index

We iterate over the same collection with $lookup. We filter all values greater than id_i.
Next step, we add 3 fields:

same_size: check if data(i) == data(j)
order1 : check if all data(j) elements are inside data(j)
it returns item(j) index [0, 1, ...]

order2 : check if all data(j) elements are inside data(i)
- it returns item(j) index inside data(i) [1, 0, ...] or [0, 1, ...]
- If not $indexOfArray returns -1
Next step, we filter:

(same_size == true) and (-1 not in order2) and (concat(order1) != concat(order2))
We remove our extra fields and final match to ignore empty differentOrder array

db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      let: {
        id: "$_id",
        data: "$data"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $gt: [
                    "$_id",
                    "$$id"
                  ]
                }
              ]
            }
          }
        },
        {
          $addFields: {
            same_size: {
              $eq: [
                {
                  $size: "$data"
                },
                {
                  $size: "$$data"
                }
              ]
            },
            order1: {
              $map: {
                input: "$data",
                in: {
                  $indexOfArray: [
                    "$data",
                    "$$this"
                  ]
                }
              }
            },
            order2: {
              $map: {
                input: "$data",
                in: {
                  $indexOfArray: [
                    "$$data",
                    "$$this"
                  ]
                }
              }
            }
          }
        },
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$same_size",
                    true
                  ]
                },
                {
                  $not: {
                    $in: [
                      -1,
                      "$order2"
                    ]
                  }
                },
                {
                  $ne: [
                    {
                      $reduce: {
                        input: "$order1",
                        initialValue: "",
                        in: {
                          $concat: [
                            "$$value",
                            {
                              $toString: "$$this"
                            }
                          ]
                        }
                      }
                    },
                    {
                      $reduce: {
                        input: "$order2",
                        initialValue: "",
                        in: {
                          $concat: [
                            "$$value",
                            {
                              $toString: "$$this"
                            }
                          ]
                        }
                      }
                    }
                  ]
                }
              ]
            }
          }
        },
        {
          $unset: [
            "order1",
            "order2",
            "same_size"
          ]
        }
      ],
      as: "differentOrder"
    }
  },
  {
    $match: {
      "differentOrder.0": {
        $exists: true
      }
    }
  }
])

https://mongoplayground.net/p/grhQX53_DJx

Collectives™ on Stack Overflow

How to find data in array of object in mongodb that have different order?

2 Answers 2

Comments

Explanation

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Explanation

Comments

Your Answer

Sign up or log in

Post as a guest

Related