Create Schema

Create Schema Using the Atlas UI

Schema generation happens automatically when you create an MongoSQL quick start connection.

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

Navigate to Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

Create a schema.

On a collection that has an empty schema:

Click the .
Click Generate new schema from sample, or provide your own JSON.
Click Save.

Create Schema Using `mongosh`

The sqlGenerateSchema command generates an MongoSQL schema for the specified collections or views.

Syntax

db.runCommand({
     sqlGenerateSchema: 1,
     sampleNamespaces: [<namespace>],
     sampleSize: <int>,
     setSchemas: true|false
})

Parameters

Parameter	Type	Description	Necessity
`sampleNamespaces`	array of strings	Specifies the comma-separated list of namespaces for which to generate schemas. A namespace includes the database name, a dot (.) separator, and the collection or view name (i.e. `<database>.<collection>\|<view>`). To generate schemas for all the collections in a database, specify `` instead of the collection or view name (i.e. `<database>.`). If omitted, generates schemas for all collections and views in the current database.	Optional
`sampleSize`	integer	Specifies the number of documents to use as a sample to create the schema. If omitted, defaults to `1000`.	Optional
`setSchemas`	boolean	Specifies whether or not to store the generated schema for the collection or view. Value can be one of the following: `true` to store the schema. If a schema already exists for the collection or view, overwrite the existing schema. `false` to not store the schema. If omitted, defaults to `false`.	Optional

Output

The command returns the following if the command succeeds.

{
      "ok" : 1,
      "schemas" : [
        {
              "databaseName" : "<database-name>",
              "namespaces" : [
                {
                      "name" : "<collection-name>",
                      "schema" : {
                        "version" : NumberLong(1),
                        "jsonSchema" : {}
                      }
                }
              ]
        },
        ...
      ]
}

The schemas object contains the following fields.

Parameter	Type	Description
`databaseName`	string	Name of the database.
`namespaces`	array of objects	Name and generated schema of each collection or view.
`namespaces.name`	string	Name of the collection or view.
`namespaces[n].schema`	document	Schema of the collection or view.
`namespaces[n].schema.version`	integer	Format version of the schema. Value is always 1.
`namespaces[n].schema.jsonSchema`	document	JSON schema of the collection or view. The JSON schema can contain the following fields: `bsonType` `properties` `items` `additionalProperties` `required` To learn more about these fields, see JSON Schema Keywords.

If you set the schema for the collection or view using the setSchemas option, you can verify that the command succeeded by running the sqlGetSchema command. The sqlGetSchema command metadata.description should contain the following value:

"set using sqlGenerateSchema with setSchemas = true"

Examples

Consider a collection named egData in a database named sampleDB with the following documents:

{"a": {"b": {"c": [1, 2, 3]}}, "s": 1}
{"a": {"b": {"c": [4, 5, 6]}}, "s": 2}
{"a": {"b": [7, 8, 9]}, "s": 3}
{"a": {"b": {"c": []}}, "s": 4}
{"a": {"b": {"c": "hello"}}, "s": 5}
{"a": {"b": {"c": {"d": 1}}}, "s": 6}
{"a": {"b": {"c": null}}}
{"s": 7}

The examples below use the sqlGenerateSchema command to generate a schema for the above collection.

Basic Example

The following command generates a schema for the collection named sampleDB.egData in the storage configuration. The command uses two randomly selected documents from the collection to create the schema because the sampleSize is 2. The command does not set the schema for the collection because the setSchemas option is not specified with the command and defaults to false.

db.runCommand({
     sqlGenerateSchema: 1,
     sampleNamespaces: ["sampleDB.egData"],
     sampleSize: 2
})

The previous command returns the following output. To learn more about the fields in the output, see Output.

{
      "ok" : 1,
      "schemas" : [
        {
              "databaseName" : "sampleDB",
              "namespaces" : [
                {
                      "name" : "egData",
                      "schema" : {
                        "version" : NumberLong(1),
                        "jsonSchema" : {
                              "bsonType" : [
                                "object"
                              ],
                              "properties" : {
                                "a" : {
                                      "bsonType" : [
                                        "object"
                                      ],
                                      "properties" : {
                                        "b" : {
                                              "bsonType" : [
                                                "object"
                                              ],
                                              "properties" : {
                                                "c" : {
                                                      "bsonType" : [
                                                        "array"
                                                      ],
                                                      "items" : {
                                                        "bsonType" : [
                                                              "int"
                                                        ]
                                                      }
                                                }
                                              }
                                        }
                                      }
                                },
                                "s" : {
                                      "bsonType" : [
                                        "int"
                                      ]
                                }
                              }
                        }
                      }
                }
              ]
        }
      ]
 }

Generate and Set Schema Example

The following command generates a schema for the collection named sampleDB.egData in the storage configuration. The command uses up to 1000 documents in the collection to create the schema because the sampleSize option is not specified with the command and defaults to 1000. The command sets the generated schema as the schema to use for the collection because the setSchemas option is set to true.

db.runCommand({
     sqlGenerateSchema: 1,
     sampleNamespaces: ["sampleDB.egData"],
     setSchemas: true
})