MongoDb Compass export db ref as embedded JSON

Question

Is there a way to export a collection with all DBRef Objects embedded in a single JSON?

I tried to export the collection from compass, but it does not resolve the dbrefs to embedded json.

Example of the data structures:

Process:

{
    "_id": {
        "$oid": "5f44e89aa1f4c77447629a29"
    },
    "name": "test process",
    "description": "A test configuration for a process",
    "milestones": [{
        "$ref": "milestone",
        "$id": {
            "$oid": "5f5753636b75033a49b7cc7b"
        }
    }, {
        "$ref": "milestone",
        "$id": {
            "$oid": "5f5753636b75033a49b7cc82"
        }
    }]
}

Milestones:

[{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc7b"
  },
  "name": "S1",
  "order": 0,
  "results": [
    {
      "$ref": "resultDefinition",
      "$id": {
        "$oid": "5f5753636b75033a49b7cc79"
      }
    },
    {
      "$ref": "resultDefinition",
      "$id": {
        "$oid": "5f5753636b75033a49b7cc7a"
      }
    }
  ]
},{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc82"
  },
  "name": "S2",
  "order": 1,
  "results": [
    {
      "$ref": "resultDefinition",
      "$id": {
        "$oid": "5f5753636b75033a49b7cc7d"
      }
    },
    {
      "$ref": "resultDefinition",
      "$id": {
        "$oid": "5f5753636b75033a49b7cc7e"
      }
    },
    {
      "$ref": "resultDefinition",
      "$id": {
        "$oid": "5f5753636b75033a49b7cc7f"
      }
    },
    {
      "$ref": "resultDefinition",
      "$id": {
        "$oid": "5f5753636b75033a49b7cc80"
      }
    },
    {
      "$ref": "resultDefinition",
      "$id": {
        "$oid": "5f5753636b75033a49b7cc81"
      }
    }
  ]
}]

Phases:

{
    "_id": {
        "$oid": "5f5753636b75033a49b7cc7c"
    },
    "name": "P1",
    "activityStream": {
        "$ref": "activityStream",
        "$id": {
            "$oid": "5f5755076b75033a49b7cc83"
        }
    }
}

ResultDefinitions:

[{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc79"
  },
  "name": "userresult",
  "resultType": "User"
},{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc7a"
  },
  "name": "planDateResult",
  "resultType": "PlanDate"
},{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc7d"
  },
  "name": "fileResult",
  "resultType": "File"
},{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc7e"
  },
  "name": "dateResult",
  "resultType": "Date"
},{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc7f"
  },
  "name": "textresult",
  "resultType": "Text"
},{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc80"
  },
  "name": "booleanResult",
  "resultType": "Boolean"
},{
  "_id": {
    "$oid": "5f5753636b75033a49b7cc81"
  },
  "name": "numberResult",
  "resultType": "Number"
}]

Relations:

Process can have n milestones. Milestone has one or no Phase. Phase has more nested paths, but not relevant for an example. Milestone can have n ResultDefinitions

you can do a aggregation query with lookup and [$out](https://docs.mongodb.com/manual/reference/operator/aggregation/out/) all result in one collection, and then you can export that collection. — turivishal, Sep 07 '20 at 09:12
will this work recursively? Say I have nested references foo -> bar --> foobar where the relation from foo to bar is 1:n — Manuel Waltschek, Sep 08 '20 at 10:55
yes this will work, also nested references will work, but depends on data, you can lookup collections using reference ids in single query, just test how much time its taking. — turivishal, Sep 08 '20 at 11:16
Can you deliver pseudo code to adapt it to a working example please? If you put this in an answer I will accept it and you'll get the bounty — Manuel Waltschek, Sep 09 '20 at 07:55
ok can you provide schemas for all collections or document structure, so i will test and update you. — turivishal, Sep 09 '20 at 07:56
by schema do you mean an export of the analyzed schema in the collection with compass, or do you mean an example in json? — Manuel Waltschek, Sep 09 '20 at 10:49
means example of fields and relation. or example of json documents for all collection, add only single single document for all collection, so that i can understand the relation fields to each other, and will make single query. — turivishal, Sep 09 '20 at 10:57

score 1 · Accepted Answer · answered Sep 10 '20 at 09:32

1

You can make a aggregation query and use $out operator, this will copy query result in separate collection, you can modify your query as per your requirement, see the last stage is $out: <collection name>, just provide the name of collection and execute this query, this will copy result in that collection, and you can export that collectoin.

db.process.aggregate([
  { $unwind: "$milestones" },
  {
    $lookup: {
      from: "milestone",
      let: { id: "$milestones.$id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$$id", "$_id"] } } },
        { $unwind: "$results" },
        {
          $lookup: {
            from: "resultDefinition",
            localField: "results.$id",
            foreignField: "_id",
            as: "results"
          }
        },
        { $unwind: "$results" },
        {
          $group: {
            _id: "$_id",
            name: { $first: "$name" },
            order: { $first: "$order" },
            results: { $push: "$results" }
          }
        }
      ],
      as: "milestones"
    }
  },
  { $unwind: "$milestones" },
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" },
      description: { $first: "$description" },
      milestones: { $push: "$milestones" }
    }
  },

  // you can specify the name of collection that you want
  { $out: "collection name" }
])

Playground

answered Sep 10 '20 at 09:32

turivishal

34,368
7
36
59

thank you! have you tried to import this into compass? (https://docs.mongodb.com/compass/master/import-pipeline-from-text/) I tried (and removed db.process.aggregate and the enclosing bracket) but it says FieldPath names may not start with '$' – Manuel Waltschek Sep 10 '20 at 10:16
1

ok what is the version of your mongodb? might be the down version is not supporting, will provide you another option. – turivishal Sep 10 '20 at 10:19
docker image: mongo:4 – Manuel Waltschek Sep 10 '20 at 10:30
1

can you try this one [Playground](https://mongoplayground.net/p/qZog-9bzjqo) – turivishal Sep 10 '20 at 11:30
Sorry for my late answer. Still not working - compass still complains about FieldPath names may not start with '$' I copied the query from your second playground link. – Manuel Waltschek Sep 11 '20 at 08:41
1

I am sorry for the problem, its causing because name of the field is `milestones.$id` that start from `$` in id, and they have corrected in 4.4 version, can you just try upgrading your compass version from [link](https://www.mongodb.com/try/download/compass) or before you can try this [playground](https://mongoplayground.net/p/BwIMGqSbMNR) just updated one more pipeline, might be this will work. or try to execute query in mongo shell, they have also provided [mongo shell](https://www.mongodb.com/try/download/shell) – turivishal Sep 11 '20 at 08:54
The mongo shell is a zip download for mac osx which includes a file without extension? What is this? I made sure I have the latest stable version of compass installed. (1.22.1 stable on the download section) and in compass it says MongoDB 4.2.8 Community. What are you using? Only the playground? I noticed that the query works in the playground, but as I am a real noob when it comes to mongodb it is really frustrating that the import does not work. – Manuel Waltschek Sep 11 '20 at 10:13
Although the import of the pipeline does not work for me, the example seems to work in playground. Therefore I will accept your answer. Maybe you can try to get it working with the newest stable version of compass that I am using (4.2.8 / 1.22.1) – Manuel Waltschek Sep 11 '20 at 10:19
1

lol so sorry for that, might be there is issue from mongo community side about without extension you can post an issue about extension to mongo developer forum or i will ask to them, look at this https://i.stack.imgur.com/ijTlX.png, same data and query executed in compass 1.21.2 and mongodb 4.4, its working, might be they have corrected in 4.4. will update you if i will get some solution. – turivishal Sep 11 '20 at 10:28

score 0 · Answer 2 · edited Sep 09 '20 at 05:15

0

You can Also Export Mongo Collection in JSON Format by this command,

mongoexport -d <db-name> -c <collection-name> --out <collection-name>.json

edited Sep 09 '20 at 05:15

Dharman

30,962
25
85
135

answered Sep 09 '20 at 05:09

Prakash Harvani

1,007
7
18

does this merge reference collection documents in single collection? – turivishal Sep 09 '20 at 05:11
It's only for Single Collection Export in JSON. – Prakash Harvani Sep 09 '20 at 07:44
1

OP wants to join reference collections document in single collection using reference ids. and your similar answers are already [here](https://stackoverflow.com/questions/8991292/dump-mongo-collection-into-json-format), no need to answer again. – turivishal Sep 09 '20 at 07:50
There is one solution for reference documents. run your query & then --out is generate the new collection. & then you can try this export JSON command. – Prakash Harvani Sep 09 '20 at 09:23
Yes that what i suggested to him, the problem is not in export. he can export clicking on the button in compass. – turivishal Sep 09 '20 at 09:25

MongoDb Compass export db ref as embedded JSON

2 Answers2