Fetching view schema...?

Working with Data
1

Hi all: I was wondering if there was a way to extract views from a collection s.t. you receive the create syntax for the view?

Using a tool, like Studio3T, I can see the syntactical representation for the view:

db.getCollection("gaDonors_don").aggregate(
    [
        { 
            "$project" : { 
                "_id" : 0.0
            }
        }
    ], 
    { 
        "allowDiskUse" : false
    }
);

What I need is a tool/command that will generate this output from the view:

db.createView(
   "gaDonors_don_view",
   "gaDonors_don",
   [ { $project: { "_id": 0 } } ],
   { "allowDiskUse: false }
)

adv(thanks)ance! :grin:

2

Hi @Micheal_Shallop,

I believe the MongoDB documentation may help answer your question:

Specifically, it says:

The view definition is public; i.e. db.getCollectionInfos() and explain operations on the view will include the pipeline that defines the view. As such, avoid referring directly to sensitive fields and values in view definitions.

Does that help?

3

Not really… if you’re familiar with PHPMyAdmin, there’s an option therein to export schema minus data.

S’why I was looking for functionality similar to what exists on the mysql side…most of the time I’m using either the cli or tools like Studio3T to create objects… then I go back and dump the create commands for those objects and embed that code into my product-deployment scripts. (Thought the explanation might help explain what I was looking for!)

4

You can use mongodump as on views it will dump out the view definition. Example, I have view “v1” on collection “people” and in my dump I get this file:

    dump/dbname/v1.metadata.json
    {"options":{"viewOn":"people","pipeline":[{"$project":{"_id":{"$numberDouble":"0.0"}}}]},"indexes":[],"uuid":""}

The above was a result of mongodump -d dbname -c v1

5

Actually a couple of additional comments.

You cannot pass {allowDiskUse:any} to view definitions. You can see some of the rationale here: https://jira.mongodb.org/browse/SERVER-27440

However, if you want to dump out a command which will create the view, you can do it like this (warning, no guarantees that this will continue to work in the future):

db.system.views.find()
{ "_id" : "demo.v1", "viewOn" : "people", "pipeline" : [ { "$project" : { "_id" : 0 } } ] }
{ "_id" : "demo.v2", "viewOn" : "people", "pipeline" : [ { "$match" : { "a" : { "$ne" : 1 } } } ], "collation" : { "locale" : "fr", "caseLevel" : false, "caseFirst" : "off", "strength" : 1, "numericOrdering" : false, "alternate" : "non-ignorable", "maxVariable" : "punct", "normalization" : false, "backwards" : false, "version" : "57.1" } }

 db.system.views.aggregate([{$replaceWith:{
       create:{$substr:["$_id", {$add:[1,{$indexOfCP:["$_id","."]}]},999]}, 
       viewOn: "$viewOn", pipeline:"$pipeline", collation:"$collation"  
 }}])
 { "create" : "v1", "viewOn" : "people", "pipeline" : [ { "$project" : { "_id" : 0 } } ] }
 { "create" : "v2", "viewOn" : "people", "pipeline" : [ { "$match" : { "a" : { "$ne" : 1 } } } ], "collation" : { "locale" : "fr", "caseLevel" : false, "caseFirst" : "off", "strength" : 1, "numericOrdering" : false, "alternate" : "non-ignorable", "maxVariable" : "punct", "normalization" : false, "backwards" : false, "version" : "57.1" } }

Here are two ways to get the data about views from the system.views collection. Note that the second version is the exact document you would pass to db.runCommand() to create the view.

P.S. I’m running 4.2 where $replaceWith is an alias for $replaceRoot:{newRoot: (slightly shorter to write)

2 Likes
6

@Asya_Kamsky - Thank you for the system queries to generate the script for reproducing the create commands - I can definitely use that in my deployment automation!

re: allowDiskUse - I’d not actually used that yet when creating views but I did think that was an acceptable option param. Now I know. :smiley:

Again, thank you for the help!

–mike

1 Like
7

Thanks very much for this, was exactly what we needed :grinning: