How to reorder MongoDB array using Prisma and NextJS

Question

I have this data in mongodb

{
  items: [
    {
      id: '6614005475802af9ae05b6b2',
      title: 'title',
      createdAt: '2024-04-08T14:33:56.734Z',

    },
    {
      id: '6613fda792ffb86a14d266ea',
      title: 'testing',
      createdAt: '2024-04-08T14:22:30.880Z',
    },
    {
      id: '6613fe3675802af9ae05b6a2',
      title: 'cool stuff',
      createdAt: '2024-04-08T14:24:54.580Z',
    }
  ]
}

How can I reorder the positions of the items, using prisma? I couldn't create a "position" field, because seems like MongoDB doesn't allow auto increment from 0/1.

Answer 1

Wrt

MongoDB doesn't allow auto increment from 0/1.

The $inc update operator does let you increment a given field. However, what you're trying to do is use incremented value for a field in another object; which $inc does not do.

My previous answer relates to adding the position field to all objects in items in all your documents.

Regardless of how you create the positions data, here's how you could insert a new object in items with the correct position value:

Assuming the data now looks like this, with the position field already created:

{
  "_id": 1,
  "items": [
    {
      "createdAt": "2024-04-08T14:33:56.734Z",
      "id": "6614005475802af9ae05b6b2",
      "position": 0,
      "title": "title"
    },
    {
      "createdAt": "2024-04-08T14:22:30.880Z",
      "id": "6613fda792ffb86a14d266ea",
      "position": 1,
      "title": "testing"
    },
    {
      "createdAt": "2024-04-08T14:24:54.580Z",
      "id": "6613fe3675802af9ae05b6a2",
      "position": 2,
      "title": "cool stuff"
    }
  ]
}

This update query will add a new object to the items array and position is auto-calculated from the size of items:

db.collection.update({ _id: 1 },
[
  {
    $set: {
      "items": {
        $concatArrays: [
          "$items",
          [
            {
              "id": "new ID here",
              "createdAt": "2024-04-09T01:23:45.678Z",
              "title": "more cool stuff",
              "position": { $size: "$items" }
            }
          ]
        ]
      }
    }
  }
])

The new object added to items looks like this, note that position is 3:

{
  "createdAt": "2024-04-09T01:23:45.678Z",
  "id": "new ID here",
  "position": 3,
  "title": "more cool stuff"
}

Mongo Playground

Notes:

1. In both queries (this one and my other answer), the position is a 0-based index. If you want to start from 1 instead of 0, then use { $add: [..., 1] } in both places where the value is set. So for this query, you would do:

"position": { $add: [{ $size: "$items" }, 1] }

2. If you expect missing objects, like after deleting the second item, but want to maintain an always increasing position or one-larger-than-last, then use the $max operator and add 1:

"position": { $add: [{ $max: "$items.position" }, 1] }

Mongo Playground. However, in this case, you're better off running the update from my previous answer, so that all position values are sequential & increasing.

Answer 2

Wrt

I couldn't create a "position" field, because seems like MongoDB doesn't allow auto increment from 0/1.

If you want to add a "position" field, then use this update query:

db.collection.update({},
[
  {
    $set: {
      "items": {
        $map: {
          input: "$items",
          as: "item",
          in: {
            $mergeObjects: [
              "$$item",
              { "position": { $indexOfArray: ["$items", "$$item"] } }
            ]
          }
        }
      }
    }
  }
],
{ multi: true }
)

Mongo Playground

Note:

• Use updateMany with this; I'm using update with {multi: true} only for Mongo playground. Also, you may not need the pipeline array syntax [] since it's only one $set step. But it doesn't work on playground without it.