2

My DB has many documents with mostly random field order as displayed in Mongo Compass. The first field is always _id but the rest of the fields could be in any order. This makes scanning records by eye very difficult. I have read that this reordering due to upserts no longer happens with Mongo 4.2 and I have upgraded - but the problem remains. Is there a way for me to reorder my fields so each document in a collection has the same field order - say -id first then a-z?

Bill
  • 618
  • 6
  • 15
  • 1
    You can try reading each document in a language that preserves hash key order, reordering fields as you see fit, then writing each document back. – D. SM Jul 28 '20 at 16:16
  • Yes but it would be a lot of work! I may investigate some migration/export methods to see if they restore to 'normal' field order. – Bill Jul 29 '20 at 14:42
  • There is no such thing as a "normal" field order. Since bson implements maps as lists of ordered key-value pairs, I would expect all regular tools to preserve the order of keys that currently exists for each individual document. – D. SM Jul 29 '20 at 15:56
  • So just order of key addition for each particular document? A schema 'sort' utility would be nice.... – Bill Jul 30 '20 at 16:37

2 Answers2

4

You can use $replaceWith to do this. https://mongoplayground.net/p/VBzpabZuJpy

db.YOURCOLLECTION.updateMany({}, [
  {$replaceWith: {
    $mergeObjects: [
      {
        "fieldA": "$fieldA",
        "fieldB": "$fieldB",
        "fieldC": "$fieldC",
        "fieldD": "$fieldD",
        "fieldE": "$fieldE"
      },
      "$$ROOT"
    ]
  }}
])
GitGitBoom
  • 1,822
  • 4
  • 5
  • thanks, your solution does indeed work! But it requires explicit naming of all the input fields or they are lost. So if I have 100 records and only one of them has a 'specialfield' then I need to add specialfield to the aggregate function or it will be lost. Is there a catchall specification to deal with this? - equivalent of anyfield : anyfield. Or a way to output all of the distinct top level fields of a collection so I can be sure I caught them all? – Bill Jul 31 '20 at 19:34
  • Yes, I just posted a edit. From https://docs.mongodb.com/manual/reference/operator/aggregation/replaceRoot/#replaceroot-with-a-new-document-created-from-root-and-a-default-document – GitGitBoom Jul 31 '20 at 20:15
  • If you still want to get all your field names anyway, take a look at this https://stackoverflow.com/a/43570730/13885615 – GitGitBoom Jul 31 '20 at 20:17
  • Thanks, the $$ROOT solved the missing field issue but in testing with your neat simulator I found out that the fields were being sorted alphabetically no matter what order was specified - so if you move fieldA to the bottom of the list it still merges first. which is fine! So, I deleted the whole specification and ran it again with just $$ROOT and it works! - ie. $mergeObjects: [ {}, "$ROOT" ] – Bill Aug 01 '20 at 21:35
  • Your solution helped me a lot. Thank you. :) – isaacsan 123 Dec 11 '21 at 23:22
0

You can try reading each document in a language that preserves hash key order, reordering fields as you see fit, then writing each document back.

Since bson implements maps as lists of ordered key-value pairs, I expect all regular tools to preserve the order of keys that currently exists for each individual document.

D. SM
  • 13,584
  • 3
  • 12
  • 21