Tuesday 26 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.
  2. Take square of all values
  3. Add all 128 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