Thursday, 28 February 2019

Sort on dynamic added field

I have 20 Millions documents in my database with the following manner.

 {
    "_id": ObjectId("5bb84e931cb3d25a3b21d14e"),
    "merchant": "menswearhouse.com",
    "category": "Fashion > Clothing > Men's Clothing",
    "feature": [
      "-0.899652959529",
      "-0.02401520125567913",
      "0.08394625037908554",
      "0.06319021433591843",
      "-0.015963224694132805"
    ]
  }

Now I have below array with which I need to find documents.

const dummy = [
  "-0.899652959529",
  "-0.02401520125567913",
  "0.08394625037908554",
  "0.06319021433591843",
  "-0.015963224694132805"
];

I need to

  1. Find difference of all the values i.e need to subtract first index of feature with the first index of my dummy array and so on for the all 5 values.
  2. Take square of all values
  3. Add all 5 values
  4. Take square root.
  5. Sort all the values with that field and get only 5 documents.

I am using this query which $projects the field when I use $limit. But I need to $sort with the $projected field and need to take top 5 documents. But there are 20 millions document it doesn't return anything and last forever.

db.collection.aggregate([
  { $project: {
    field: {
      $sqrt: {
        $sum: {
          $map: {
            input: { $range: [0, { $size: '$feature' }] },
            as: "d",
            in: {
              $pow: [
                {
                  $subtract: [
                    { $toDouble: { $arrayElemAt: [dummy, "$$d"] }},
                    { $toDouble: { $arrayElemAt: ["$feature", "$$d"] }}
                  ]
                },
                2
              ]
            }
          }
        }
      }
    }
  }}
])

Can I use index on the field which is being created at the runtime?

Thanks!!!



from Sort on dynamic added field

No comments:

Post a Comment